how to use a variable in a CASE statement?

Question

Post reply

how to use a variable in a CASE statement?

water490

Ten Centuries

Points: 1205

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24608 More actions · Answer 1

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 nvarchar(MAX);

SET @SQL = N'
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(nvarchar(10), @AVERAGE_LENGTH -1) + N' PRECEDING AND CURRENT ROW) = ' + CONVERT(nvarchar(10), @AVERAGE_LENGTH) + N'
                      THEN AVG(T1.CLOSE_PRICE)   OVER (PARTITION BY T1.SYMBOL
                                                       ORDER BY T1.TRADE_DATE
                                                       ROWS BETWEEN ' + CONVERT(nvarchar(10), @AVERAGE_LENGTH -1) + N' 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.

water490 Ten Centuries Points: 1205 More actions · Answer 2

I ran the code and it gave an error:

Msg 214, Level 16, State 2, Procedure sys.sp_executesql, Line 1 [Batch Start Line 2]

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Do you know how to fix this?

pietlinden SSC Guru Points: 63616 More actions · Answer 3

force it to NTEXT?

[ @stmt = ] N'statement'

A Unicode string that contains a Transact-SQL statement or batch. @stmt must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, aren't allowed. Character constants aren't allowed. Unicode constants must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' isn't. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Source: sp_executesql (Transact-SQL) - SQL Server | Microsoft Learn

water490 Ten Centuries Points: 1205 More actions · Answer 4

water490

Ten Centuries

Points: 1205

September 30, 2025 at 2:18 am

#4661157

Thanks guys. It works.