How to use variable in Select clause [closed]

I need way to call variable in select clause. Eg. Table name “TableList” contains the list of table names.

DECLARE @CounterNumber INT = 1
Declare @name varchar(255)
Set @name = (Select Name from TableList where id = @CounterNumber)
WHILE @CounterNumber <= 3

select top 10 * from @name
set @CounterNumber = @CounterNumber + 1

Error Message – Must declare the table variable “@name”.

I used “quotename()” as well but still getting the same error.

Answer

You would need to use dynamic SQL:

WHILE @CounterNumber <= 3
BEGIN
    SELECT @name Name 
    FROM TableList 
    WHERE id = @CounterNumber;

    DECLARE @sql = NVARCHAR(MAX);

    SET @sql = 'select top 10 * from @name';

    SET @sql = REPLACE(@sql, '@name', quotename(@name));

    EXEC sp_executesql @sql;

    SET @CounterNumber = @CounterNumber + 1;
END;

Advertisement

Answer