[thelist] TIp - dotNet: Using Null with Stored Procedures....

[Anthony Baratta]

<tip author="Anthony Baratta" subject="dotNet: Using Null with Stored 
Procedures">

So, when is null NOT null? When you are trying to pass a null parameter to 
a Stored Procedure with dotNet.

We have several classes built that assist us in interacting with the Stored 
Procedures in our MS SQL Server. Until recently we did not need to worry 
about whether the parameter being passed contained a null value. Of course 
today we need the value to be null, and after cleaning up our classes to 
support carrying the null to the SqlCommand object, we ran into some issues 
with getting the final parameter to equal null.

First we tried this:

foreach (DictionaryEntry de in sParams)
{

     SqlParameter genParam;
     genParam = cmd.Parameters.Add(de.Key.ToString(), SqlDbType.VarChar, 500);
     if (de.Value == null)
     {
         genParam.IsNullable = true;
         genParam.Value = null;
     } else if (de.Value == "") {
         genParam.Value = "";
     } else {
         genParam.Value = SQLScrubber(de.Value.ToString());
     }
}

But each time we called the class we would get an error like "Parameter 
@ParamName Not Found" from the SP and we knew that @ParamName was there - 
it just wasn't being handed over to the SP. After trying a bazillion 
options I uncovered the following alternate null value: System.DBNull.Value.

Now we have:

foreach (DictionaryEntry de in sParams)
{

     SqlParameter genParam;
     genParam = cmd.Parameters.Add(de.Key.ToString(), SqlDbType.VarChar, 500);
     if (de.Value == null)
     {
         genParam.IsNullable = true;
         genParam.Value = System.DBNull.Value;
     } else if (de.Value == "") {
         genParam.Value = "";
     } else {
         genParam.Value = SQLScrubber(de.Value.ToString());
     }
}

This works like a charm. I'm assuming that setting the genParam.Value = 
null actually "removes" it from the list. By using System.DBNull.Value we 
keep the parameter around with a null value.

Hopefully this will help someone.
</tip>
-- 
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."