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_7357084 · 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 => returns SOMETEXT regardless of @m
-- n=2 and m=1 => returns SOMEOTHERTEXT
-- n=2 and m=2 => returns SOMEOTHERTEXTGOESHERE
-- n=2 and m>2 => returns null (no else defined for inner case)

user_995506 · 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

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

4 Answers