How to use SET operation in CASE clause in SQL? I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: 


  Incorrect syntax near SET


Query:

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE 
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT; 

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE 
                    WHEN @TrendValue &gt; @Increasing THEN 0
                    WHEN @TrendValue &gt;  @StableStart 
                        AND @TrendValue &lt;  @StableEND THEN 1
                    WHEN @TrendValue &lt; @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond = 
        CASE 
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE 
                        WHEN @TrendValue &gt; @Increasing THEN 0
                        WHEN @TrendValue &gt;  @StableStart 
                            AND @TrendValue &lt;  @StableEND THEN 1
                        WHEN @TrendValue &lt; @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird = 
        CASE 
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE 
                        WHEN @TrendValue &gt; @Increasing THEN 0
                        WHEN @TrendValue &gt;  @StableStart 
                            AND @TrendValue &lt;  @StableEND THEN 1
                        WHEN @TrendValue &lt; @Declining THEN 2
                    END
                )
        END
FROM Result R;


It would be better if there is another way to store the result of the expression into @TrendValue so that there is no need to calculate that expression each time.

It would also be good to avoid division by zero errors (that's the reason I added the various R.TtlNOfCmpSale7to12 != 0 checks).
Based on your comment, you seem to be looking for a way to avoid repeating the same calculation over and over again.

You could use APPLY VALUES to calculate the required values once, then use the aliased column in the rest of your calculations.

DECLARE @Declining FLOAT,
@StableStart SMALLINT,
@StableEnd SMALLINT,
@Increasing SMALLINT

SELECT  @Declining = 0.90,
@StableStart = 90,
@StableEnd = 110,
@Increasing = 110

DECLARE @TrendValue FLOAT; 

UPDATE R
SET
 R.TtlNOfCmpSaleSpanFirst = 
            CASE 
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendFirst &gt; @Increasing THEN 0
                        WHEN Trends.TrendFirst &gt;  @StableStart AND Trends.TrendFirst &lt;  @StableEND THEN 1
                        WHEN Trends.TrendFirst &lt; @Declining THEN 2
                  END
            END,
 R.TtlNOfCmpSaleSpanSecond = 
            CASE 
                WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                  CASE WHEN Trends.TrendSecond &gt; @Increasing THEN 0
                        WHEN Trends.TrendSecond &gt;  @StableStart AND Trends.TrendSecond &lt;  @StableEND THEN 1
                        WHEN Trends.TrendSecond &lt; @Declining THEN 2
                  END
            END,

 R.TtlNOfCmpSaleSpanThird = 
            CASE 
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendThird &gt; @Increasing THEN 0
                        WHEN Trends.TrendThird &gt;  @StableStart AND Trends.TrendThird &lt;  @StableEND THEN 1
                        WHEN Trends.TrendThird &lt; @Declining THEN 2
                  END
            END

FROM Result R
CROSS APPLY (
    VALUES (IIF(R.TtlNOfCmpSale7to12 &lt;&gt; 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12, 0)
              IIF(R.TtlNOfCmpSale4to6 &lt;&gt; 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6, 0) 
              IIF(R.TtlNOfCmpSale7to12 &lt;&gt; 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12, 0)
) AS Trends(TrendFirst, TrendSecond, TrendThird);