January 19, 2011 at 6:54 am
I posted a previous query where I was looking just for a state, now I want to use a case in the where clause based on a parameter. The way it is now I keep getting syntax errors.
declare @StateFilter int
set @statefilter = 1
....
WHERE CASE WHEN @statefilter in (0,1) THEN 1=1 WHEN @statefilter = 2 THEN state = 'CA' WHEN @statefilter = 3 THEN state <> 'CA' END
January 19, 2011 at 7:29 am
Maybe you want something like:
WHERE [state] =
CASE
WHEN @statefilter = 2
THEN 'CA'
WHEN @statefilter = 3 AND [state] = 'CA'
THEN 'ZZ' -- junk value
ELSE [state]
END
January 20, 2011 at 3:20 pm
Yeah, SQL doesn't do boolean values like in C based languages. The case statement returns a value that must be compared to something else to get a boolean.
http://msdn.microsoft.com/en-US/library/ms181765(v=SQL.90).aspx
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply