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.