Procedure or function expects parameter which was not supplied : But its there.

This is a strange issue but a real one. One would normally assume that when executing a SQL server stored procedure, passing "null" as a value for a parameter which has data type as varchar/nvarchar is intuitive. However if you do it, you will get exception with a message like following:

 

"System.Data.SqlClient.SqlException: Procedure or function 'DoSomethingWithString' expects parameter '@inputString', which was not supplied."

 

Fix: It is actually pretty straightforward. Pass DBNull.Value instead of "null".

 

Here is the sample code that you can try.

 

Create a database named "Test" on local database server's default instance. Create a stored procedure in it using following script:

 

Create Procedure dbo.DoSomethingWithString
(
 @inputString NVARCHAR(256)
)
AS
BEGIN
 SET NOCOUNT ON

 SELECT @inputString AS 'Output'

END

 

Create a console application and add the following method:

 

 private static void TestNullStringParameter()
{
            string connectionString = "data source=(local);initial catalog=Test;integrated security=True;";

            try
            {
                using (IDbConnection conn = new SqlConnection(connectionString))
                {
                    using (IDbCommand command = conn.CreateCommand())
                    {
                        command.CommandText = "dbo.DoSomethingWithString";
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        //command.Parameters.Add(new SqlParameter("inputString", DBNull.Value));
                        command.Parameters.Add(new SqlParameter("inputString", null));
                        conn.Open();
                        using (IDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                        {
                            if (reader.Read())
                            {
                                Console.WriteLine(reader[0]);
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception!!");
                Console.WriteLine(e);
            }
}