Design  HTML

Latest News:

Technology:

n 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
Skip Navigation Links
© 2024 LiquidCreations.MediaCenter