Problems with CASE - 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)

Problems with CASE

Author

Topic

marchaq
Starting Member

2 Posts

Posted - 2011-01-28 : 14:55:46

Hi,

I'm having a trouble getting the IN/CASE statement to work in the WHERE clause. The query is much more complicated, but it really boils down to this piece:

SELECT * FROM TABLE
WHERE
status IN
(
CASE @status WHEN NULL THEN ('1','2','3')
ELSE @status
END
)

@status is setup by a user picking up a value from a drop-down list in the app. Status can be 1, 2, 3 or NULL. The problem is that the query must return results for all three statuses if the user does not make a selection (@status is null). I'm getting a syntax errors near ',' (comma). No idea how to make it happen. Any help would be truly appreciated! Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-28 : 15:09:27

where ( @status is null and [status] in ('1','2','3')
)
or
[status] = @status

Jim

Everyday I learn something that somebody else already knew

marchaq
Starting Member

2 Posts

Posted - 2011-02-03 : 13:06:28

Jim,

Thanks! This should work real nice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources