Procedure or function 'pu_usr_User' expects parameter '@WhiteIp', which was not supplied.

遇到这个问题，是因为存储过程的参数，设置默认值写错了。

错误写法

  @WhiteIp NVARCHAR(MAX) NULL,

 

Stored procedure with default parameters

I wrote with parameters that are predefined

They are not "predefined" logically, somewhere inside your code. But as arguments of SP they have no default values and are required. To avoid passing those params explicitly you have to define default values in SP definition:

Alter Procedure [Test]
    @StartDate AS varchar(6) = NULL, 
    @EndDate AS varchar(6) = NULL
AS
...

NULLs or empty strings or something more sensible - up to you. It does not matter since you are overwriting values of those arguments in the first lines of SP.

Now you can call it without passing any arguments e.g. exec dbo.TEST

 

修正之后的写法

 @WhiteIp NVARCHAR(MAX) = NULL,

 

The parameterized query ..... expects the parameter '@units', which was not supplied

回答1

Try this code:

SqlParameter unitsParam = command.Parameters.AddWithValue("@units", units);
if (units == null)
{
    unitsParam.Value = DBNull.Value;
}

And you must check all other parameters for null value. If it null you must pass DBNull.Value value.

 

 回答2

Here's a way using the null-coalescing operator:

cmd.Parameters.AddWithValue("@units", units ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@range", range ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@scale", scale ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@description", description ?? (object)DBNull.Value);

Or for more strict type checking:

cmd.Parameters.Add("@units", SqlDbType.Int).Value = units ?? (object)DBNull.Value;
cmd.Parameters.Add("@range", SqlDbType.Int).Value = range ?? (object)DBNull.Value;
cmd.Parameters.Add("@scale", SqlDbType.Int).Value = scale ?? (object)DBNull.Value;
cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = description ?? (object)DBNull.Value;

The operator also be chained:

int?[] a = { null, null, 1 };
Console.WriteLine(a[0] ?? a[1] ?? a[2]);