SqlParameter, a SELECT statement, a WHERE clause and DBNull.Value

Question

4.00/5 (1 vote)

See more:

Hi all,
I have a sql statement that I have parameterized like this:

C#

string sql = "SELECT * FROM xyz WHERE col1 = @val1 AND col2 = @val2";

Everything works fine until I have to deal with a null value for @val2 that I assign like this:

C#

SqlParameter para1 = new SqlParameter("@val1", DBNull.Value)

The SELECT statement returns no rows.
If I change the query to:

C#

string sql = "SELECT * FROM xyz WHERE col1 IS NULL AND col2 = @val2";

it works fine.

I know that checking for NULL in a sql statement requires to use "IS NULL" but how should I deal with that using a parameterized query in order to get the same effect?

Any help is kindly appreciated,
best regards
Andy

Posted 24-Apr-12 23:19pm

hoernchenmeister

Updated 24-Apr-12 23:32pm

v2

Add a Solution

2 solutions

Solution 2

Hi,

Remove '=' sign before IS NULL

C#

string sql = "SELECT * FROM xyz WHERE col1 IS NULL AND col2 = @val2";

Posted 24-Apr-12 23:27pm

Deepak_Sharma_

Comments

hoernchenmeister 25-Apr-12 5:32am

Thanks for pointing this out, that was just a typo :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-04-24T23:26:00

Solution 1

SQL

SELECT * FROM xyz WHERE (@VAL1 IS NULL OR col1 IS NULL OR col1 = @val1) AND col2 = @val2

Posted 24-Apr-12 23:26pm

OriginalGriff

Comments

hoernchenmeister 25-Apr-12 5:44am

Thanks for takning the time OriginalGriff,
If I understand this query right it also returns rows where col1 is not only null. Did I get this right?

I checked it:
declare @pn nvarchar(256)
set @pn = null

SELECT * FROM table1
WHERE col2 = 'fubar' AND (@pn IS NULL OR col1 IS NULL OR col1 = @pn)

it should behave like this:
SELECT * FROM table1 WHERE col2 = 'fubar' AND col1 IS NULL

As I am filling a SqlCommand I pass the null value via DBNull.Value...
I hope I could explain this right :)

OriginalGriff 25-Apr-12 6:01am

Sorry, I'm not sure I understand what you are trying to say (it's probably me, I have a cold).
What values are you trying to return, for what inputs?

hoernchenmeister 25-Apr-12 6:17am

Nevermind, it's probably me beeing lost in translation ;)

Basically I fire queries against a Sql database using SqlConnection/SqlCommand/SqlDataReader.
For that purpose I created a parameterized query:

SELECT * FROM xzy WHERE col1 = @val1

I now "fill" the parameters using SqlParameter

string sVal1 = "fubar";
SqlParameter para1 = new SqlParameter("@val1", (String.IsNullOrEmpty(sVal1) ? DBNull.Value : (object)sVal1));

That works, the problem comes up when I have to assign a null value lie this:
string sVal1 = null;
SqlParameter para1 = new SqlParameter("@val1", (String.IsNullOrEmpty(sVal1) ? DBNull.Value : (object)sVal1));

In that case the parameters value is set to DBNull.Value.

Unfortunately I now do not get the values I expected.

In plain SQL I would write:
SELECT * FROM xzy WHERE col1 IS NULL
instead of:
SELECT * FROM xzy WHERE col1 = NULL

So maybe this is the cause for not getting rows...

So I was wondering if there is a way how such a query should be constructed if the Where clause might be bugged with null values.

Just let me know if you have questions or if anything is unclear... and of course get well soon ;)

Cheers
Andy

OriginalGriff 25-Apr-12 6:43am

Ah! It's gonna look ugly (You can't use logic statements in WHERE clauses)
SELECT * FROM xyz WHERE (@VAL1 IS NULL AND col1 IS NULL) OR (@VAL1 IS NOT NULL AND col1 = @val1)

hoernchenmeister 25-Apr-12 7:37am

Too bad...
I didn't thought of that, but your query suerly archives the goal ;)
I still think a little about just constructing the WHERE clause dynamically so that I can put in "is null" if necessary, but I'll see.

Thanks OriginalGriff for your help, it is very much appreciated,
cheers
Andy

OriginalGriff 25-Apr-12 7:46am

You're welcome!