how to use a variable in a CASE statement?

• water490

  Ten Centuries

  Points: 1150

  More actions

  September 29, 2025 at 4:32 am

  #4660533

  hi everyone

  I am getting an error when I add a variable to a CASE statement.  How can I fix it?

  Code:

  DECLARE @AVERAGE_LENGTH INT
  SET @AVERAGE_LENGTH = 3
  
  SELECT T1.SYMBOL,
  T1.TRADE_DATE,
  CASE WHEN COUNT(T1.CLOSE_PRICE) OVER (PARTITION BY T1.SYMBOL ORDER BY T1.TRADE_DATE ROWS BETWEEN @AVERAGE_LENGTH - 1 PRECEDING AND CURRENT ROW) = @AVERAGE_LENGTH
   THEN AVG(T1.CLOSE_PRICE) OVER (PARTITION BY T1.SYMBOL ORDER BY T1.TRADE_DATE ROWS BETWEEN @AVERAGE_LENGTH - 1 PRECEDING AND CURRENT ROW)
   ELSE NULL
  END AS AVERAGE
  FROM DBO.TestTable AS T1

  Error:

  Msg 156, Level 15, State 1, Line 4
  Incorrect syntax near the keyword 'as'.
  Msg 102, Level 15, State 1, Line 28
  Incorrect syntax near '@AVERAGE_LENGTH'.
  

  Thank you