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...
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.
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
