Case Statement in where clause on a bit field
-
Hi,
I have a bit field (OrderProcessed) in Order table. I want to query the table on this bit field and retrieve the rows based on the user input. Here is my query:
-- @caseFlag is the user input.
Declare @CaseFlag int
SET @CaseFlag = 1
SELECT * FROM Orders
WHERE OrderProcessed = CASE @CaseFlag WHEN 0 THEN 0
WHEN 1 THEN 1
END
All works fine when the user input (@CaseFlag) is 1 or 0. But how do I query the table if the user wants to see all the orders, (OrderProcessed = true and false). (note: @CaseFlag will have a value of 2 which means all rows.)
Any help is highly appreciated.
Thanks,
Satya
-
Case statement has a else clause too. see if you can use the else clausefor that. else one idea is check if @caseflag has a value of 1 or 0 if not don't process the where clause which is more easier.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Sugesh,
Thanks for the reply. I am not sure how to mention both 0 and 1 for the @CaseFlag in the else clause.
Can you please give me an example?
My where clause has other conditions too. I had to concise it for readability. So I cannot not process my where clause. Can you give me an example as to how to eliminate just that condition (OrderProcessed = ? ) from the where clause on requirement basis?
Thanks,
Satya
-
Can't this be re-written as
using the in clause
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I tried that. But I doubt SQL Server will accept that. It gave me syntax error
-
Hope this helps you
Declare
@CaseFlag int
SET
@CaseFlag = 2
if
@caseflag = 0 or @caseflag = 1
begin
SELECT
* FROM Orders
WHERE
OrderProcessed = CASE @CaseFlag WHEN 0 THEN 0
WHEN
1 THEN 1
--else (0,1)
END
end
else
begin
SELECT
* FROM Orders
end
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Try this:
SELECT * FROM Orders
WHERE (OrderProcessed = CASE @CaseFlag WHEN 0 THEN 0
WHEN 1 THEN 1
END)
or (@CaseFlag = 2)
-
Actually, try this:
WHERE (OrderProcessed = @CaseFlag) or (@CaseFlag = 2)
-
Thanks Lynn. That worked
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply