Case expression to set a variable that is then used in another case expression [duplicate]

Question

I have the following SQL query (VERY simplified):

SELECT
    Column1,
    Column2,
    Column3,
    Column4,
    CASE
        WHEN [Field1] = Condition1 THEN Value1
        WHEN [Field1] = Condition2 THEN Value2
        ELSE Value2
    END AS "Label1", 
    CASE @Var
        WHEN Condition 1 THEN
            CASE [Field2]
                WHEN Condition1 THEN Value1
                WHEN Condition2 THEN Value2
                WHEN Condition3 THEN Value3
                WHEN Condition4 THEN Value4
                ELSE Value2
            END
        ELSE
            CASE [Field2]
                WHEN Condition5 THEN Value5
                WHEN Condition6 THEN Value6
                WHEN Condition7 THEN Value7
                WHEN Condition8 THEN Value8
                ELSE Value2
            END
    END AS "Label2",

I need to use the result of the first CASE expression to evaluate the second CASE expression. I thought that using a variable (@Var) would be the best way, however I can't get the result of the first CASE expression assigned to the variable.

I have tried:

SET @Var in front of the CASE clause
SET @Var = in each WHEN clause
Removing the AS Label1 from the end of the CASE expression
Making two copies of the CASE expression, using one of the variable and the other for the Field1 value

Every attempt to add the variable to the first CASE expression returns an error. I've been able to add the nested CASE expression (and test it) properly, but I need to be able to examine the result of the first CASE expression in order to properly set the second.

I'm not fixed on using a variable to accomplish this requirement.

Lukasz Szozda · Accepted Answer · 2025-07-23 17:34:13Z

2

To reuse "intermediate results" at the same level CROSS APPLY can be used:

SELECT
  Column1,
  Column2,
  Column3,
  Column4,
  s.Label1,

  CASE s.label1
    WHEN Condition 1 THEN
        CASE [Field2]
            WHEN Condition1 THEN Value1
            WHEN Condition2 THEN Value2
            WHEN Condition3 THEN Value3
            WHEN Condition4 THEN Value4
            ELSE Value2
        END
    ELSE
        CASE [Field2]
            WHEN Condition5 THEN Value5
            WHEN Condition6 THEN Value6
            WHEN Condition7 THEN Value7
            WHEN Condition8 THEN Value8
            ELSE Value2
        END
  END AS Label2
FROM ...
CROSS APPLY (
  SELECT CASE
    WHEN [Field1] = Condition1 THEN Value1
    WHEN [Field1] = Condition2 THEN Value2
    ELSE Value2
  END
) AS s(Label1)

answered Jul 23 at 17:34

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2025-07-23 23:14:23Z

In SQL Server there is no 'variable' concept within the context of a single SQL statement (i.e. you can't define, assign, and use a variable within a single SQL statement). You can evaluate the first case expression in a subquery and then use it in a parent query, like this:

With Part1 as (
    SELECT
        Column1,
        Column2,
        Column3,
        Column4,
        CASE
            WHEN [Field1] = Condition1 THEN Value1
            WHEN [Field1] = Condition2 THEN Value2
            ELSE Value2
        END AS Label1, 
        Field2
    FROM yrTable
)
SELECT  
    P1.*,
    CASE Label1
        WHEN Condition1 THEN
            CASE [Field2]
                WHEN Condition1 THEN Value1
                ...
                ELSE Value2
            END
        ELSE
            CASE [Field2]
                WHEN Condition5 THEN Value5
                ...
                ELSE Value2
            END
    END AS Label2
from Part1 P1

This uses a CTE (Common Table Expression) to evaluate the first CASE expression, and then refers to it in the main query. Alternatively the subquery can be in the FROM clause.

Just to pre-empt, this is unlikely to impact performance because the server can merge these before processing, but it is by no means guaranteed (you need to check/test if you are concerned about the performance).

Also, SQL Server 2012 and up, the IIF() function provides a more compact way of writing some CASE expressions.

Collectives™ on Stack Overflow

Case expression to set a variable that is then used in another case expression [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related