Ignore if null value? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ignore if null value?

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 03:09:45

Dear Gurus,

In Store Proceedures, how do I skip the value if the WHERE condition is null? I think if not skipped, it is considered as IS NULL

Example:

EXEC my_SP null, 123456

-- inside my_SP:
SELECT * FROM my_table WHERE 0=0
AND Value1 = @Value1
AND Value2 = @Value2

Devart
Posting Yak Master

102 Posts

Posted - 2011-03-28 : 03:29:20

quote:
Originally posted by calvinfoo

Dear Gurus,

In Store Proceedures, how do I skip the value if the WHERE condition is null? I think if not skipped, it is considered as IS NULL

Example:

EXEC my_SP null, 123456

-- inside my_SP:
SELECT * FROM my_table WHERE 0=0
AND Value1 = @Value1
AND Value2 = @Value2

For example:

-- inside my_SP:
SELECT *
FROM my_table
WHERE
CASE WHEN @Value1 IS NULL THEN -999 ELSE Value1 END = CASE WHEN @Value1 IS NULL THEN 0 ELSE @Value1 END AND
CASE WHEN @Value2 IS NULL THEN -999 ELSE Value2 END = CASE WHEN @Value2 IS NULL THEN 0 ELSE @Value2 END

or

-- inside my_SP:
SET @value1 = isnull(@value1,-999)
SET @value2 = isnull(@value2,-999)

SELECT * FROM my_table WHERE 0=0
AND Value1 = @Value1
AND Value2 = @Value2

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 03:52:55

I think this is not what I am asking for...
What I mean is:

Example #1:
EXEC my_SP null, 123456
SELECT * FROM my_table WHERE Value2 = @Value2

Example #2:
EXEC my_SP 123456, NULL
SELECT * FROM my_table WHERE Value1 = @Value1

Example #3:
EXEC my_SP 123, 456
SELECT * FROM my_table WHERE Value1 = @Value1 AND Value2 = @Value2

Example #4:
EXEC my_SP NULL, NULL
SELECT * FROM my_table

Devart
Posting Yak Master

102 Posts

Posted - 2011-03-28 : 04:01:52

Sorry,

You can try this:

WHERE
CASE WHEN @Value1 IS NULL THEN 0 ELSE Value1 END = CASE WHEN @Value1 IS NULL THEN 0 ELSE @Value1 END AND
CASE WHEN @Value2 IS NULL THEN 0 ELSE Value2 END = CASE WHEN @Value2 IS NULL THEN 0 ELSE @Value2 END

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-28 : 05:00:52

http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx
go through this link this might do it

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 05:32:36

Thanks, It works! :)

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 20:53:17

SET @value1 = isnull(@value1,-999)
SET @value2 = isnull(@value2,-999)

SELECT * FROM my_table WHERE 0=0
AND Value1 = @Value1
AND Value2 = @Value2

The above doesn't work...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-28 : 20:56:25

quote:
Originally posted by calvinfoo
The above doesn't work...

When you said "doesn't work" you have to explain what does not work ? what is the value of @Value1 and @Value2 that you pass in to your query ?

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 21:05:47

CASE WHEN @Value1 IS NULL THEN 0 ELSE Value1 END = CASE WHEN @Value1 IS NULL THEN 0 ELSE @Value1 END AND
CASE WHEN @Value2 IS NULL THEN 0 ELSE Value2 END = CASE WHEN @Value2 IS NULL THEN 0 ELSE @Value2 END

The above works, and I slowly read it and I can underdstand how it works... but can it be simplied further? The seems complicated with two CASEs

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 21:12:35

quote:

When you said "doesn't work" you have to explain what does not work ? what is the value of @Value1 and @Value2 that you pass in to your query ?

Sorry, what I mean is, I tested it, it returned the result as if the query as below, it takes both Value1 and Value2 without ignoring either one:

SELECT * FROM my_table WHERE Value1 = @Value1 AND Value2 = @Value2

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 21:16:52

quote:
Sorry, what I mean is, I tested it, it returned the result as if the query as below, it takes both Value1 and Value2 without ignoring either one:

SELECT * FROM my_table WHERE Value1 = @Value1 AND Value2 = @Value2

Example:
EXEC my_SP 123, NULL returned:
SELECT * FROM my_table WHERE Value1 = 123 AND Value2 = NULL

But I wanted is
SELECT * FROM my_table WHERE Value1 = 123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-28 : 21:16:59

why don't you just use the method in the link provided by ahmeds08 ?

I have also posted similar method in your other thread

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-28 : 21:19:56

quote:
Originally posted by calvinfoo

quote:

When you said "doesn't work" you have to explain what does not work ? what is the value of @Value1 and @Value2 that you pass in to your query ?

Sorry, what I mean is, I tested it, it returned the result as if the query as below, it takes both Value1 and Value2 without ignoring either one:

SELECT * FROM my_table WHERE Value1 = @Value1 AND Value2 = @Value2

the problem with your query is you didn't handle NULL value properly. You can't compare NULL using EQUAL operator which you did in your query. For NULL value, you have to use IS NULL or IS NOT NULL


WHERE somecol IS NULL

and not


WHERE somecol = NULL

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-28 : 21:24:56

dear khTan,

Argh! Stupid me! Thanks for the reminder! I completely forgotten about your solution previously! Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources