I have a stored procedure that I'm passing a value in the variable @status and I want to do conditional logic to create the proper where clause. If @status is 0 then the where clause should be: where status_int <> 9. If it's 1 then the where clause is: where status_int=2. If it's 2 then the where clause is: where status_int in (1,-1).
I've written something like this but it gives a syntax error... oddly enough on the "<" and the second "when"
select * from license
where case @status
when 0 then status_int <> 9
when 1 then status_int = 2
when 2 then status_int in (1,-1)
I've tried other variations but just can't seem to get it right. How can I fix this?