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.
