CODEWITHSUNDEEP
sql-serversqlcommand
Drew McGhie
Drew McGhie

Reputation: 1086

SqlCommand, passing something that could be null

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.

Upvotes: 3

Views: 2809

Answers (3)

Carlos Cocom
Carlos Cocom

Reputation: 932

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)

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294227

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.

Upvotes: 2

Related Questions