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
Case in Where Clause
