Case in Where Clause

acko · #1 (**permalink**) June 22nd, 2003, 03:04 PM

Hi everybody
Can I use case statement in WHERE clause.
For example

DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE CASE @VAR1
          WHEN 'Customers' THEN CustomerID = @VAR2
          WHEN 'Employee' THEN EmployeeID = @VAR2
          END

This is not working OK.
Can someone explain me the right way?
Thanks.
Best regards
Alex

Jeff Mason · #2 (**permalink**) June 22nd, 2003, 05:01 PM

In your situation, where you wish to select on two entirely different columns depending on a parameter, it is probably better to use an IF/ELSE construction, as:

Code:

IF @Var1='Customers' THEN
   SELECT * FROM Orders WHERE CustomerID=@Var2;
ELSE
   SELECT * FROM Orders WHERE EmployeeID=@Var2;

It's important to realize that CASE is an expression and not an executable statement.

The general form of the predicate of a WHERE clause is:

Code:

 expression operator expression

where operator is = or <> or > or < etc. The WHERE clause in your query does not follow this form, which is why you were having problems.

If you insist on using the CASE expression, then you can try a couple of ugly alternatives, like:

Code:

   SELECT * FROM Orders
    WHERE CustomerID=CASE @Var1 WHEN 'Customers' THEN @Var2 ELSE 0 END
       OR EmployeeID=CASE @Var1 WHEN 'Employees' THEN @Var2 ELSE 0 END

assuming that 0 is not a legal value for either 'CustomerID' or 'EmployeeID'. You could also try a nested CASE like:

Code:

   SELECT * FROM Orders
    WHERE 1 = 
      CASE @Var1
         WHEN 'Customers' THEN CASE WHEN CustomerID=@Var2 THEN 1 ELSE 0 END
         WHEN 'Employees' THEN CASE WHEN EmployeeID=@Var2 THEN 1 ELSE 0 END
      END

I think that the CASE expression doesn't work well in this situation and that the IF/ELSE is simpler and easier to understand.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

acko · #3 (**permalink**) June 23rd, 2003, 09:31 AM

Thanks Very Much
Alex

craigpardey · #4 (**permalink**) August 6th, 2004, 03:43 PM

I think this should work for you...

SELECT * FROM ORDERS
WHERE
(
  CASE
    WHEN @VAR1 = 'Customers' AND CustomerID = @VAR2 THEN 1
    WHEN @VAR1 = 'Employee' AND EmployeeID = @VAR2 THEN 1
    ELSE 0
  END
) = 1

sbacon · #5 (**permalink**) September 18th, 2008, 09:57 AM

or you could do this ...

SELECT *
FROM ORDERS
WHERE (@VAR1 = 'Customers' and CustomerID = @VAR2) OR
(@VAR1 = 'Employee' and EmployeeID = @VAR2)

Old Pedant · #6 (**permalink**) September 18th, 2008, 03:56 PM

Jeff's first answer is best (though it doesn't handle an illegal value for @VAR1) and SBacon's answer is a close second, but Jeff's other answers and Craig's answer are all overkill.

Why not just:

Code:

DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE @VAR2 = ( CASE @VAR1
                WHEN 'Customers' THEN CustomerID 
                WHEN 'Employee'  THEN EmployeeID 
                ELSE -1
                END )

The ELSE is in there to protect against an illegal value for @VAR1 of course.

Jeff Mason · #7 (**permalink**) September 18th, 2008, 04:31 PM

Quote:

quote:Originally posted by Old Pedant
...Jeff's other answers ... are all overkill.

Well... I did say they were "...a couple of ugly alternatives..." ;)

(Any idea why we're responding to a 5 year old original post? :D )

Jeff Mason
je.mason@comcast.net

Old Pedant · #8 (**permalink**) September 18th, 2008, 04:50 PM

> (Any idea why we're responding to a 5 year old original post?

Ummm...because I inanely didn't look at the original message date and because SBacon is a complete newbie and how the heck did he *find* this thread in the first place and... Well, maybe Google sent him this way??? But then why is he responding instead of asking? And... Ehhh, leave it at "because I inanely...".

Jeff Mason · #9 (**permalink**) September 18th, 2008, 04:59 PM

Quote:

quote:Originally posted by Old Pedant
> And... Ehhh, leave it at "because I inanely...".

:D

Jeff Mason
je.mason@comcast.net

Need to download code?

Navigation