Case in where clause

I have a store procedure and what I am trying to do is to say if the state eq 4 then exclude type DC.

WHERE SM.officeStateKey = @stateKey
AND CASE WHEN SM.officeStateKey = 4
		THEN  SM.type != 'DC'
		
		END type

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
Try this:
WHERE SM.officeStateKey = @stateKey
AND  (SM.type <> 'DC' OR SM.officeStateKey <> 4)

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This is more obvious, though dsacker's condition is correct:
WHERE SM.officeStateKey = @stateKey
AND (SM.officeStateKey = 4 and SM.type != 'DC'
        or SM.officeStateKey != 4)

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Re your CASE: always keep in mind the whole CASE is no condition (as an IF), but an expression. It has to result in a value.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< No points for this comment, as the question has already been answered, but if you read my article and 'like' it that would be super.. >

For an image and code-heavy tutorial on CASE blocks please read my article on SQL Server CASE Solutions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.