SQL Server 2008 - Case If statements in SELECT Clause

Question

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

User · Answer

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

User · Answer

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

User · Answer

CASE is the answer  but you will need to have a separate case statement for each column you want returned  As long as the WHERE clause is the same  there won t be much benefit separating it out into multiple queries   Example   SELECT     CASE  var         WHEN  xyz  THEN col1         WHEN  zyx  THEN col2         ELSE col7     END      CASE  var         WHEN  xyz  THEN col2         WHEN  zyx  THEN col3         ELSE col8     END FROM Table

User · Answer

The most obvious solutions are already listed   Depending on where the query is sat  i e  in application code  you can t always use IF statements and the inline CASE statements can get painful where lots of columns become conditional  Assuming Col1   Col3   Col7 are the same type  and likewise Col2  Col4   Col8 you can do this   SELECT Col1  Col2 FROM tbl WHERE  Var LIKE  xyz  UNION ALL SELECT Col3  Col4 FROM tbl WHERE  Var LIKE  zyx  UNION ALL SELECT Col7  Col8 FROM tbl WHERE  Var NOT LIKE  xyz  AND  Var NOT LIKE  zyx    As this is a single command there are several performance benefits with regard to plan caching   Also the Query Optimiser will quickly eliminate those statements where  Var doesn t match the appropriate value without touching the storage engine

User · Answer

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

User · Answer

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

[sql] SQL Server 2008 - Case / If statements in SELECT Clause

Examples related to sql

Examples related to sql-server

Examples related to tsql