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

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

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

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

Examples related to tsql

Examples related to case-when