SQL Server 2008 - Case / If statements in SELECT Clause
Solution 1
Just a note here that you may actually be better off having 3 separate SELECTS for reasons of optimization. If you have one single SELECT then the generated plan will have to project all columns col1, col2, col3, col7, col8 etc, although, depending on the value of the runtime @var, only some are needed. This may result in plans that do unnecessary clustered index lookups because the non-clustered index Doesn't cover all columns projected by the SELECT.
On the other hand 3 separate SELECTS, each projecting the needed columns only may benefit from non-clustered indexes that cover just your projected column in each case.
Of course this depends on the actual schema of your data model and the exact queries, but this is just a heads up so you don't bring the imperative thinking mind frame of procedural programming to the declarative world of SQL.
Solution 2
You are looking for the CASE statement
http://msdn.microsoft.com/en-us/library/ms181765.aspx
Example copied from MSDN:
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
Solution 3
Try something like
SELECT
CASE var
WHEN xyz THEN col1
WHEN zyx THEN col2
ELSE col7
END AS col1,
...
In other words, use a conditional expression to select the value, then rename the column.
Alternately, you could build up some sort of dynamic SQL hack to share the query tail; I've done this with iBatis before.
Solution 4
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Reference: http://msdn.microsoft.com/en-us/library/ms181765.aspx
Tejaswi Yerukalapudi
Updated on July 05, 2022Comments
-
Tejaswi Yerukalapudi about 2 years
I have a Query that's supposed to run like this -
If(var = xyz) SELECT col1, col2 ELSE IF(var = zyx) SELECT col2, col3 ELSE SELECT col7,col8 FROM . . .
How do I achieve this in T-SQL without writing separate queries for each clause? Currently I'm running it as
IF (var = xyz) { Query1 } ELSE IF (var = zyx) { Query2 } ELSE { Query3 }
That's just a lot of redundant code just to select different columns depending on a value. Any alternatives?