3

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'???

Olivarsham
  • 1,701
  • 5
  • 25
  • 51
  • @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 09 '12 at 09:29
  • perhaps also see: http://stackoverflow.com/a/9632050/23354 – Marc Gravell Mar 09 '12 at 09:57

1 Answers1

10

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
    ...
}
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 2
    +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 09 '12 at 09:29
  • 2
    @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 09 '12 at 09:30
  • @MarcGravell - couldn't agree more – AdaTheDev Mar 09 '12 at 09: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 09 '12 at 09:37
  • @AdaTheDev you might find this interesting then: http://stackoverflow.com/a/9632050/23354 – Marc Gravell Mar 09 '12 at 09:56