Table name as variable

Question

I am trying to execute this query:

declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename

This produces the following error:

Msg 1087, Level 16, State 1, Line 5

Must declare the table variable "@tablename".

What's the right way to have table name populated dynamically?

Answer 1

You'll need to generate the sql dynamically:

declare @tablename varchar(50) 

set @tablename = 'test' 

declare @sql varchar(500)

set @sql = 'select * from ' + @tablename 

exec (@sql)

Answer 2

Bit late for an answer but should help out someone else:

CREATE PROCEDURE [dbo].[GetByName]
    @TableName NVARCHAR(100)
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sSQL nvarchar(500);

    SELECT @sSQL = N'SELECT * FROM' + QUOTENAME(@TableName);

    EXEC sp_executesql @sSQL



END