stored procedure expects parameter which was not supplied

Question

Hello,

I'm calling a stored procedure from C# .net and one of the string parameters is null. When that is the case, I pass DBNull.Value. However, I get the above error. Any ideas?

Answer 1

You get this if the value of a parameter is "null" (as opposed to DBNull.Value).

Are you sure the parameter value is DBNull.Value?

Answer 2

If the string is null, you will see this error. To avoid it, you can set a default parameter value in your stored proc, or you can pass DBNull.Value if your string is null.

Answer 3

Can you give more details like how you are calling the sproc, the parameter itself and the value. You know in your sproc you can set default values for variables.

Something to the effect of:

ALTER SprocMySproc
 @myvar varchar(50)=NULL

SELECT blah FROM MyTable WHERE MyField=@myvar OR @myvar IS NULL

Your actual C# or vb.net code can then ignore sending the parameter if it is null or empty

if(!(String.IsNullOrEmpty(myVar)))
 {
  //pass the parameter
  mySQLCommandObject.Parameters.Add("@myvar", sqldbtype.varchar).Value = myVar;
  //other code...
 }

Answer 4

Do you have access to the Stored Procedure? If so, (And if the Stored procedure logic will allow it), modify the declaration of the input parameter to add " = Null" at the end, as in

 Create procedure ProcName 
 @MyParameterName Integer = Null,
  -- con't 

 As...