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

• DesNorton

  SSC-Insane

  Points: 24572

  More actions

  September 29, 2025 at 6:30 am

  #4660570

  You cant use a variable here

  ROWS BETWEEN @AVERAGE_LENGTH - 1 PRECEDING AND CURRENT ROW)

  You can achieve what you are attempting by using Dynamic SQL

  DECLARE @AVERAGE_LENGTH int;
  SET @AVERAGE_LENGTH = 3;
  
  DECLARE @SQL varchar(MAX);
  
  SET @SQL = '
  SELECT T1.SYMBOL
       , T1.TRADE_DATE
       , AVERAGE = CASE WHEN COUNT(T1.CLOSE_PRICE) OVER (PARTITION BY T1.SYMBOL
                                                         ORDER BY T1.TRADE_DATE
                                                         ROWS BETWEEN ' + CONVERT(varchar(10), @AVERAGE_LENGTH) + ' - 1 PRECEDING AND CURRENT ROW) = ' + CONVERT(varchar(10), @AVERAGE_LENGTH) + '
                        THEN AVG(T1.CLOSE_PRICE)   OVER (PARTITION BY T1.SYMBOL
                                                         ORDER BY T1.TRADE_DATE
                                                         ROWS BETWEEN ' + CONVERT(varchar(10), @AVERAGE_LENGTH) + ' - 1 PRECEDING AND CURRENT ROW)
                        ELSE NULL END
  FROM DBO.TestTable AS T1;'
  
  --PRINT(@SQL);
  EXEC sys.sp_executesql @stmt = @SQL;

  ---

  How to post data/code on a forum to get the best help.