Developers usually come across a situation wherein they would want to use a variable in a T-SQL Statement which accepts only constant values.
For example, if we try to assign a variable to a Select Top <@variable_name> statement, we would get an error like:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘@variable_name’
There is a work around this problem though. The below stored procedure solves it:
CREATE PROCEDURE TEST
@TestInt INT
AS
BEGIN
DECLARE @SqlQuery NVARCHAR(4000);
SET @SqlQuery = ‘SELECT TOP ‘+CAST(@TestInt as CHAR)+’* FROM TABLE_TEST’;
EXEC(@SqlQuery)
END
