SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Ignore if null value?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  03:09:45  Show Profile  Reply with Quote
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

Edited by - calvinfoo on 03/28/2011 03:16:53

Devart
Posting Yak Master

102 Posts

Posted - 03/28/2011 :  03:29:20  Show Profile  Visit Devart's Homepage  Reply with Quote
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
Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  03:52:55  Show Profile  Reply with Quote
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




Go to Top of Page

Devart
Posting Yak Master

102 Posts

Posted - 03/28/2011 :  04:01:52  Show Profile  Visit Devart's Homepage  Reply with Quote
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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
649 Posts

Posted - 03/28/2011 :  05:00:52  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx
go through this link this might do it
Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  05:32:36  Show Profile  Reply with Quote
Thanks, It works! :)

Edited by - calvinfoo on 03/28/2011 05:46:25
Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  20:53:17  Show Profile  Reply with Quote
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...
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 03/28/2011 :  20:56:25  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  21:05:47  Show Profile  Reply with Quote
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
Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  21:12:35  Show Profile  Reply with Quote
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
Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  21:16:52  Show Profile  Reply with Quote
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


Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 03/28/2011 :  21:16:59  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 03/28/2011 :  21:19:56  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

calvinfoo
Posting Yak Master

Malaysia
129 Posts

Posted - 03/28/2011 :  21:24:56  Show Profile  Reply with Quote
dear khTan,

Argh! Stupid me! Thanks for the reminder! I completely forgotten about your solution previously! Thanks again!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000