Using Variables in T-SQL Statements that accept only constant values

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