How do I use T-SQL s Case When

Question

I have a huge query which uses case when often  Now I have this SQL here  which does not work     select case when xyz something   1  then       SOMETEXT   else        select case when xyz somethingelse   1        then            SOMEOTHERTEXT        end           select case when xyz somethingelseagain   2        then            SOMEOTHERTEXTGOESHERE        end   end   ColumnName     Whats causing trouble is xyz somethingelseagain   2  it says it could not bind that expression  xyz is some alias for a table which is joined further down in the query  Whats wrong here  Removing one of the 2 case whens corrects that  but I need both of them  probably even more cases

User · Answer

If logical test is against a single column then you could use something like

USE AdventureWorks2012;  
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

More information - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

User · Answer

declare  n int   7       m int   3   select      case          when  n   1 then              SOMETEXT      else         case              when  m   1 then                  SOMEOTHERTEXT              when  m   2 then                  SOMEOTHERTEXTGOESHERE          end     end as col1 -- n 1   gt  returns SOMETEXT regardless of  m -- n 2 and m 1   gt  returns SOMEOTHERTEXT -- n 2 and m 2   gt  returns SOMEOTHERTEXTGOESHERE -- n 2 and m gt 2   gt  returns null  no else defined for inner case

User · Answer

SELECT    CASE     WHEN xyz something   1 THEN  SOMETEXT     WHEN xyz somethingelse   1 THEN  SOMEOTHERTEXT     WHEN xyz somethingelseagain   2 THEN  SOMEOTHERTEXTGOESHERE     ELSE  SOMETHING UNKNOWN     END AS ColumnName

User · Answer

As soon as a WHEN statement is true the break is implicit   You will have to concider which WHEN Expression is the most likely to happen  If you put that WHEN at the end of a long list of WHEN statements  your sql is likely to be slower  So put it up front as the first   More information here  break in case statement in T-SQL

[tsql] How do I use T-SQL's Case/When?

Examples related to tsql

Examples related to case-when