如何在 SQL 的 CASE 子句中使用 SET 操作？

我有以下結構，我SET在一個子句中進行操作，CASE但它產生了一個錯誤：

附近的語法不正確SET

詢問：

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 > @Increasing THEN 0
                   WHEN @TrendValue >  @StableStart 
                       AND @TrendValue <  @StableEND THEN 1
                   WHEN @TrendValue < @Declining THEN 2
               END
           )
   END,
   R.TtlNOfCmpSaleSpanSecond = 
       CASE 
           WHEN R.TtlNOfCmpSale4to6!= 0 THEN
               SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
               (
                   CASE 
                       WHEN @TrendValue > @Increasing THEN 0
                       WHEN @TrendValue >  @StableStart 
                           AND @TrendValue <  @StableEND THEN 1
                       WHEN @TrendValue < @Declining THEN 2
                   END
               )
       END,
   R.TtlNOfCmpSaleSpanThird = 
       CASE 
           WHEN R.TtlNOfCmpSale7to12 != 0 THEN
               SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
               (
                   CASE 
                       WHEN @TrendValue > @Increasing THEN 0
                       WHEN @TrendValue >  @StableStart 
                           AND @TrendValue <  @StableEND THEN 1
                       WHEN @TrendValue < @Declining THEN 2
                   END
               )
       END
FROM Result R;

如果有另一種方法將表達式的結果儲存到@TrendValue 中，這樣就無需每次都計算該表達式。

避免除以零錯誤也很好（這就是我添加各種R.TtlNOfCmpSale7to12 != 0檢查的原因）。

根據您的評論，您似乎正在尋找一種方法來避免一遍又一遍地重複相同的計算。

您可以使用APPLY VALUES一次來計算所需的值，然後在其餘計算中使用別名列。

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 > @Increasing THEN 0
                       WHEN Trends.TrendFirst >  @StableStart AND Trends.TrendFirst <  @StableEND THEN 1
                       WHEN Trends.TrendFirst < @Declining THEN 2
                 END
           END,
R.TtlNOfCmpSaleSpanSecond = 
           CASE 
               WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                 CASE WHEN Trends.TrendSecond > @Increasing THEN 0
                       WHEN Trends.TrendSecond >  @StableStart AND Trends.TrendSecond <  @StableEND THEN 1
                       WHEN Trends.TrendSecond < @Declining THEN 2
                 END
           END,

R.TtlNOfCmpSaleSpanThird = 
           CASE 
               WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                 CASE WHEN Trends.TrendThird > @Increasing THEN 0
                       WHEN Trends.TrendThird >  @StableStart AND Trends.TrendThird <  @StableEND THEN 1
                       WHEN Trends.TrendThird < @Declining THEN 2
                 END
           END

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

引用自：https://dba.stackexchange.com/questions/127688