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.

score 108 · Accepted Answer · edited Jun 20 '13 at 20:33

108

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;

edited Jun 20 '13 at 20:33

p.campbell

98,673
67
256
322

answered Jul 27 '10 at 09:45

Florian Reischl

3,788
1
24
19

1

Works, whats the difference? :) – grady Jul 27 '10 at 09:50
4

Lets say I have some of the whens as duplicates, like this: WHEN xyz.something = 1 and xyz.abc = 2 THEN 'SOMETEXT' WHEN xyz.something = 1 and xyz.abc <> 2 THEN 'SOMEOTHERTEXT' Can I somehow say that if the first when was set that the upcoming ones are skipped? Something which escapes the case/when? – grady Jul 27 '10 at 10:07
The original query has multiple SELECT statements, so MS SQL server is expecting subqueries. It an easy mistake to make if you're used to some programming languages that use select and case rather than CASE and WHEN. – Martin Eyles Jul 06 '23 at 16:55

score 8 · Answer 2 · edited May 23 '17 at 10:30

8

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

edited May 23 '17 at 10:30

Community

1
1

answered Oct 30 '12 at 14:57

Per Hoffmann Olsen

191
1
8

1

This is true a lot of the time, but is not guaranteed: http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression – RobD Aug 22 '16 at 16:26

score 1 · Answer 3 · answered Jan 10 '18 at 10:59

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)

score 1 · Answer 4 · answered Jan 28 '19 at 09:44

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://learn.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 Answers4

Linked