Can you use CASE in a WHERE clause?
-
Is it possible to use a CASE statement in a where clause? I'm trying to avoid writing multiple select statements, so I have a query where an ID can optionally be passed in, like this:
CREATE PROC _someProc (@ID INT=0)
SET NOCOUNT ON
SELECT blah, blah FROM blah
-- PSEUDOCODE BELOW --
IF @ID > 0 THEN
Use a WHERE clause like "WHERE pkID=@ID"
ELSE
Don't use a WHERE clause, pull back all rows
END
-- END PSEUDOCODE --
So, does that make sense to any of you SQL Gurus?
THANKS!!!
-
try using the following statement
SELECT blah, blah FROM blah (CASE WHEN @ID > 0 THEN WHERE pkID=@ID END)
Could someone tell me if we can have more than 1 case in a case statement. Like
IF @ID > 0 THEN
Use a WHERE clause like "WHERE pkID=@ID"
ELSEIF @ID > 4
Don't use a WHERE clause, pull back all rows
Else
Do something else
END
-
Yes, you can use CASE in a WHERE clause:
SELECT *
FROM YourTable
WHERE pkID =
CASE @ID
WHEN 0 THEN pkID
ELSE @ID
END
This:
>>try using the following statement
>>SELECT blah, blah FROM blah (CASE WHEN @ID > 0 THEN WHERE pkID=@ID END)
... doesn't work, it's an example of trying to use dynamic SQL.
-
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply