IF/CASE in WHERE Clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

IF/CASE in WHERE Clause

Author

Topic

MakWebster
Starting Member

3 Posts

Posted - 2007-06-16 : 00:30:09

Hi,

I am trying to write this rather simple query and cant get it to work:

DECLARE @T INT
SET @R = T -- 0|1|2
SELECT * FROM TEST WHERE TestID in (1,2)
AND
CASE WHEN @R = 1 THEN TestDate IS NULL
ELSE TestDate is NOT NULL
END

Basically what i am trying to achieve is that @T can be assigned only 0,1 or 2 and based on this i have to select TestDate column which can have either NULL or NOT NULL value.

so i can say if @t is 0 show all, when 1 show not nulls when 2 show nulls.

Any clever way of doing this?

Help is much appreciated

Mak

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 00:38:39

Simple approach


DECLARE @T INT 
SET @R = T -- 0|1|2

IF @R = 1 
    SELECT * FROM TEST WHERE TestID in (1,2)
    AND TestDate IS NULL 
ELSE
    SELECT * FROM TEST WHERE TestID in (1,2)
    AND TestDate IS NOT NULL

Madhivanan

Failing to plan is Planning to fail

MakWebster
Starting Member

3 Posts

Posted - 2007-06-16 : 06:03:27

Thanks for the reply,
the stored procedure has got several select statements each one of them has different WHERE clause and this check is generic that need to go with all the statements.

thats why i like to do inline check witin the WHERE clause

re-writing proc is not really an option

Any more ideas ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-16 : 07:37:23

Just use simple, proper boolean logic:

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

If you are not really strong with how ANDs and ORs and boolean expressions work, you should really spend some time practicing and reading up on them -- they are very important to understand thoroughly when writing and SQL statements. (or doing any computer programming, for that matter)

- Jeff
http://weblogs.sqlteam.com/JeffS

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-16 : 08:38:36

[code]
DECLARE @T INT
SET @R = @T -- 0|1|2
SELECT * FROM TEST WHERE TestID in (1,2)
AND
(
(
@R = 1 And TestDate IS NULL
)
OR
(
@R = 2 And TestDate is NOT NULL
)
)

[/code]

Chirag

http://chirikworld.blogspot.com/

MakWebster
Starting Member

3 Posts

Posted - 2007-06-18 : 10:00:17

Thanks to all particularly to chiragkhabaria, this is exactly what i wanted

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources