How to use SET operation in CASE clause in SQL?
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);