存储过程或函数需要未提供的参数

I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error

我试图通过调用存储过程将数据插入SQL Server数据库，但我收到错误

Procedure or function 'SHOWuser' expects parameter '@userID', which was not supplied.

过程或函数'SHOWuser'需要参数'@userID'，这是未提供的。

My stored procedure is called SHOWuser. I have checked it thoroughly and no parameters is missing.

我的存储过程称为SHOWuser。我已经彻底检查了它，没有参数丢失。

My code is:

我的代码是：

public void SHOWuser(string userName, string password, string emailAddress, List<int> preferences)
{
        SqlConnection dbcon = new SqlConnection(conn);

        try
        { 
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = dbcon;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SHOWuser";

            cmd.Parameters.AddWithValue("@userName", userName);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@emailAddress", emailAddress);

            dbcon.Open();

            int i = Convert.ToInt32(cmd.ExecuteScalar());

            cmd.Parameters.Clear();
            cmd.CommandText = "tbl_pref";

            foreach (int preference in preferences)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i));
                cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference));

                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            dbcon.Close();
        }

and the stored procedure is:

并且存储过程是：

ALTER PROCEDURE [dbo].[SHOWuser]
(
    @userName varchar(50),
    @password nvarchar(50),
    @emailAddress nvarchar(50)
)
AS
BEGIN
    INSERT INTO tbl_user(userName, password, emailAddress) 
    VALUES (@userName, @password, @emailAddress)

    SELECT
        tbl_user.userID, tbl_user.userName,
        tbl_user.password, tbl_user.emailAddress, 
        STUFF((SELECT ',' + preferenceName 
               FROM tbl_pref_master
               INNER JOIN tbl_preferences ON tbl_pref_master.preferenceID = tbl_preferences.preferenceID
               WHERE tbl_preferences.userID = tbl_user.userID
               FOR XML PATH ('')), 1, 1, ' ' ) AS Preferences
    FROM
        tbl_user

    SELECT SCOPE_IDENTITY();
END

This is the second stored procedure tbl_pref which is used in the same function:

这是在同一函数中使用的第二个存储过程tbl_pref：

ALTER PROCEDURE [dbo].[tbl_pref]
    @userID int,
    @preferenceID int
AS
BEGIN
    INSERT INTO tbl_preferences(userID, preferenceID) 
    VALUES (@userID, @preferenceID)
END

5 个解决方案

#1

Your stored procedure expects 5 parameters as input

您的存储过程需要5个参数作为输入

@userID int, 
@userName varchar(50), 
@password nvarchar(50), 
@emailAddress nvarchar(50), 
@preferenceName varchar(20)

So you should add all 5 parameters to this SP call:

因此，您应该将所有5个参数添加到此SP调用：

    cmd.CommandText = "SHOWuser";
    cmd.Parameters.AddWithValue("@userID",userID);
    cmd.Parameters.AddWithValue("@userName", userName);
    cmd.Parameters.AddWithValue("@password", password);
    cmd.Parameters.AddWithValue("@emailAddress", emailAddress);
    cmd.Parameters.AddWithValue("@preferenceName", preferences);
    dbcon.Open();

PS: It's not clear what these parameter are for. You don't use these parameters in your SP body so your SP should looks like:

PS：目前尚不清楚这些参数的用途。您不在SP正文中使用这些参数，因此您的SP应如下所示：

ALTER PROCEDURE [dbo].[SHOWuser] AS BEGIN ..... END

#2

111

In my case I received this exception even when all parameter values were correctly supplied but the type of command was not specified :

在我的情况下，即使正确提供了所有参数值但未指定命令类型，我也收到了此异常：

cmd.CommandType = System.Data.CommandType.StoredProcedure;

This is obviously not the case in the question above, but exception description is not very clear in this case, so I decided to specify that.

在上面的问题中显然不是这种情况，但在这种情况下异常描述不是很清楚，所以我决定指定它。

#3

I came across this issue yesterday, but none of the solutions here worked exactly, however they did point me in the right direction.

我昨天遇到过这个问题，但这里的解决方案都没有完全奏效，但他们确实指出了我正确的方向。

Our application is a workflow tool written in C# and, overly simplified, has several stored procedures on the database, as well as a table of metadata about each parameter used by each stored procedure (name, order, data type, size, etc), allowing us to create as many new stored procedures as we need without having to change the C#.

我们的应用程序是一个用C＃编写的工作流工具，过于简化，在数据库上有几个存储过程，以及每个存储过程使用的每个参数的元数据表（名称，顺序，数据类型，大小等），允许我们根据需要创建尽可能多的新存储过程，而无需更改C＃。

Analysis of the problem showed that our code was setting all the correct parameters on the SqlCommand object, however once it was executed, it threw the same error as the OP got.

对问题的分析表明我们的代码在SqlCommand对象上设置了所有正确的参数，但是一旦执行它，就会抛出与OP相同的错误。

Further analysis revealed that some parameters had a value of null. I therefore must draw the conclusion that SqlCommand objects ignore any SqlParameter object in their .Parameters collection with a value of null.

进一步分析显示，某些参数的值为null。因此，我必须得出结论，SqlCommand对象忽略其.Parameters集合中任何值为null的SqlParameter对象。

There are two solutions to this problem that I found.

我发现这个问题有两种解决方案。

In our stored procedures, give a default value to each parameter, so from @Parameter int to @Parameter int = NULL (or some other default value as required).

在我们的存储过程中，为每个参数指定一个默认值，因此从@Parameter int到@Parameter int = NULL（或者根据需要使用其他一些默认值）。
In our code that generates the individual SqlParameter objects, assigning DBNull.Value instead of null where the intended value is a SQL NULL does the trick.

在我们生成单个SqlParameter对象的代码中，指定DBNull.Value而不是null，其中目标值是SQL NULL。

The original coder has moved on and the code was originally written with Solution 1 in mind, and having weighed up the benefits of both, I think I'll stick with Solution 1. It's much easier to specify a default value for a specific stored procedure when writing it, rather than it always being NULL as defined in the code.

最初的编码器已经开始了，代码最初是用解决方案1编写的，并且已经权衡了两者的好处，我想我会坚持使用解决方案1.为特定存储过程指定默认值要容易得多在编写它时，而不是总是在代码中定义的NULL。

Hope that helps someone.

希望能帮助别人。

#4

In my case, It was returning one output parameter and was not Returning any value.
So changed it to

在我的情况下，它返回一个输出参数，并没有返回任何值。所以改成了

param.Direction = ParameterDirection.Output;
command.ExecuteScalar();

and then it was throwing size error. so had to set the size as well

然后它抛出尺寸错误。所以不得不设置大小

SqlParameter param = new SqlParameter("@Name",SqlDbType.NVarChar);
param.Size = 10;

#5

In my case I got the error on output parameter even though I was setting it correctly on C# side I figured out I forgot to give a default value to output parameter on the stored procedure

在我的情况下，我得到输出参数的错误，即使我在C＃端正确设置我发现我忘了给存储过程输出参数的默认值

ALTER PROCEDURE [dbo].[test]
(
                @UserID int,
                @ReturnValue int = 0 output --Previously I had @ReturnValue int output
)

#1