Procedure Expects Parameter Which Was Not Supplied

SQL parameters are awesome. They automatically protect you from SQL injection attacks. They do have one strange caveat, though: you cannot pass a null value to a SQL parameter. Doing so will give you this interesting and unhelpful exception:

Procedure or function "..." expects parameter '@foo', which was not supplied

This doesn’t make sense when you have code like:

string foo = getSomeFoo(); // May return null
SqlCommand cmd = new SqlCommand("SELECT * FROM someTable WHERE foo = @foo");
cmd.Parameters.AddWithValue("@foo", foo);
cmd.ExecuteNonQuery(); // Throws exception

You can see that @foo is, indeed, supplied. But in the case where the value is null, you will see an exception.

So what’s the solution? You can make sure that foo has a value, or some non-null value (like empty string or DBNull.Value), or you can conditionally add the parameter when needed.

And calling a stored procedure with a parameter that may be null? That, my friends, is a question for Google.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Core .NET, Web, Wndows Forms, WPF and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *