Is it possible to use a Case statement in a sql From clause

Is it possible to use a Case statement in a sql From clause using SQL 2005? For example, I'm trying something like:

SELECT Md5 FROM CASE WHEN @ClientType = 'Employee' THEN @Source = 'HR' WHEN @ClientType = 'Member' THEN @Source = 'Other' END CASE WHERE Current = 2; 

5 Answers

I don't believe that's possible. For one thing, query optimizers assume a specific list of table-like things in the FROM clause.

The most simple workaround that I can think of would be a UNION between the two tables:

SELECT md5 FROM hr WHERE @clienttype = 'Employee' AND current = 2 UNION SELECT md5 FROM other WHERE @clienttype = 'Member' AND current = 2; 

Only one half of the UNION could be True, given the @clienttype predicate.

Assuming SQL Server:

You would need to use dynamic SQL. Build the string and then call sp_executesql with the string.

Edit: Better yet, just use if statements to execute the appropriate statement and assign the value to a variable. You should avoid dynamic SQL if possible.

No, you can't pick a table to query using a CASE statement. CASE statements only go within expressions, such as for a column's value or as part of your WHERE expression.

This should do it, if you are looking for just one value:

IF @ClientType = 'Employee' BEGIN SET @Source = (SELECT Md5 FROM HR WHERE Current = 2) END ELSE IF @ClientType = 'Member' BEGIN SET @Source = (SELECT Md5 FROM Other WHERE Current = 2) END 

Since you don't specify what SQL backend you're going against, this will be hard to properly answer....

As far as I can tell, you will not be able to do this neither against MS SQL Server, nor against Interbase/Firebird. I can't speak for other backend servers, though...

Marc

I think it's pretty safe to say the answer is no way. And that's regardless of SQL dialect.