Take the 2-minute tour ×

Is is possible to pass DBNull.Value as optional parameter? I tried in C#

private void CallSP(string param0, string param2 = DBNull.Value)

Its is giving error. I need to call a Stored procedure in this method, so i am passing DBNull.Value. Is'DBNull.Value' and 'null' are treated as same in SQL Server? Shall i pass 'null' instead of 'DBNull.Value'???

share|improve this question
@1ntello "never use null" is contrary - there's absolutely no reason not to use null, and it isn't not very different to the null in SQL - only null-equality is different, and that is trivial to make consistent. –  Marc Gravell Mar 9 '12 at 9:29
perhaps also see: stackoverflow.com/a/9632050/23354 –  Marc Gravell Mar 9 '12 at 9:57
add comment

1 Answer

up vote 8 down vote accepted

DBNull.Value is not a string, and is not a constant (optional parameter values must be constants). The way to do this is to default it to regular null, and handle the null when adding the parameter:

private void CallSP(string param0, string param2 = null)
    object value = (object)param2 ?? DBNull.Value;
    // ^^^ use value in the parameter
share|improve this answer
+1, just to state explicitly for OP, as it frequently trips people up - setting a parameter value to null will result in that parameter not being passed and you'd end up with a SqlException. null != DBNull.Value –  AdaTheDev Mar 9 '12 at 9:29
That is a beautiful solution.. –  Henk Jansen Mar 9 '12 at 9:30
@AdaTheDev personally, I have yet to find a compelling reason for the existence of DBNull. I've seen many arguments - none of them very compelling. –  Marc Gravell Mar 9 '12 at 9:30
@MarcGravell - couldn't agree more –  AdaTheDev Mar 9 '12 at 9:36
@MarcGravell That's OK. Personally I can never see any compelling reasons to make varchar fields nullable. But someone somewhere out there may think things like that are useful! –  Mr Lister Mar 9 '12 at 9:37
show 1 more comment

Your Answer


