SqlCommand, passing something that could be null

Question

I have a SQL command that I've been asked to modify, and I'm having some troubles with the fact that what I'm passing to the SQL can now be null. If I'm passing a value, I can rely on the columnName = @parameterName in the SQL, but with NULL, I can't pass null or DBNull and have it correctly resolve.

Here's the SQL pseudocode:

SELECT
  Columns
FROM
  ClientSetup
WHERE
  Client_Code = @ClientCode AND
  Package_Code = @PackageCode AND
  Report_Code = @ReportCode

The problem is that now @ReportCode can validly be NULL. In my C# code where I set up the SqlCommand, I can put in:

cmd.Parameters.Add("@ReportCode", SqlDBType.VarChar, 5).Value = reportType;
  //reportType is a string, which can be null

But, if reportType is null, I need to use Report_Code IS NULL in the SQL, rather than Report_Code = @reportCode.

The solution I've found is to change the last where clause to the following:

((@ReportCode IS NULL AND Report_Code IS NULL) OR Report_Code = @ReportCode)

and the parameter phrase to

cmd.Parameters.Add("@ReportCode", SqlDBType.VarChar, 5).Value = string.IsNullOrEmpty(reportType) ? System.DBNull : reportType;

What this does works, but I was wondering if anyone knew of a cleaner or better way to handle nullable parameters when passing things to SQL from .NET code.

score 2 · Accepted Answer · answered Nov 16 '11 at 21:00

The short answer is that no, the SqlClient API requires you to pass in a DbNull.Value for NULL parameter values.

But I have some doubts about how you treat NULLs. For one you use string.IsNullOrEmpty which means that you treat the emtpy string as a NULL. This is questionable, there may be legitimate empty string values in the database.

My second concern is the logic of matching NULLs in the database. More often than not passing in a NULL parameter means that the request is interested in any value, not specifically in NULL values. I'm not saying your logic of matching NULL parameters to NULL values is flawed, I just want to make sure you know what you're doing.

The code isn't pretty, and what I'm being asked to do isn't to make it pretty, just to add an additional feature. Throughout the entire project, Null here is treated as "Nothing" rather than "Anything", and I'm keeping consistent with the (admittedly un-optimal) coding conventions in place. I also don't have to worry (now) about "" being an entry in that field, but I've commented my code to point that out. — Drew McGhie, Nov 16 '11 at 21:51

score 0 · Answer 2 · answered Nov 16 '11 at 21:03

Much of a muchness, in the past I've built the query

test reportcode for null if is its replace ReportCode = @ReportCode with "ReportCode is Null" add the reportcode parameter if it isn't.

Generally though ReportCode of null signalled I wanted to select based on the other parameters and didn't care what the null one was.

It's a bit naughty

but Where IsNull(ReportCode,'') = IsNull(@ReportCode,'')

would give you what you want, given you are using IsNullOrEmpty.

score 0 · Answer 3 · answered Nov 16 '11 at 21:04

Well, is correct the way how you fix it

only is (@ReportCode IS NULL AND Report_Code IS NULL) not is neccesary. because it is not c# o c++.

Some how that should be finally result

 SELECT
   Columns
 FROM
   ClientSetup
 WHERE
   Client_Code = @ClientCode
   AND Package_Code = @PackageCode
   AND (Report_Code = @ReportCode or @ReportCode is null)

SqlCommand, passing something that could be null

3 Answers3