A table name as a variable

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 the table name populated dynamically?

Use `sp_executesql` to execute any SQL, e.g.

DECLARE @tbl sysname,
@sql nvarchar(4000),
@params nvarchar(4000),
@count int

DECLARE tblcur CURSOR STATIC LOCAL FOR
SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
ORDER BY 1
OPEN tblcur

WHILE 1 = 1
BEGIN
FETCH tblcur INTO @tbl
IF @@fetch_status <> 0
BREAK

SELECT @sql =
N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
N' WHERE LastUpdated BETWEEN @fromdate AND ' +
N' coalesce(@todate, ''99991231'')'
SELECT @params = N'@fromdate datetime, ' +
N'@todate datetime = NULL, ' +
N'@cnt int OUTPUT'
EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

DEALLOCATE tblcur

Declare @tablename varchar(50)
set @tablename = 'Your table Name'
EXEC('select * from ' + @tablename)

You can't use a table name for a variable. You'd have to do this instead:

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from yourtable'
EXEC (@sqlCommand)

You'll need to generate the SQL content dynamically:

declare @tablename varchar(50)

set @tablename = 'test'

declare @sql varchar(500)

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

exec (@sql)

Change your last statement to this:

EXEC('SELECT * FROM ' + @tablename)

This is how I do mine in a stored procedure. The first block will declare the variable, and set the table name based on the current year and month name, in this case TEST_2012OCTOBER. I then check if it exists in the database already, and remove if it does. Then the next block will use a SELECT INTO statement to create the table and populate it with records from another table with parameters.

--DECLARE TABLE NAME VARIABLE DYNAMICALLY
DECLARE @table_name varchar(max)
SET @table_name =
(SELECT 'TEST_'
+ DATENAME(YEAR,GETDATE())
+ UPPER(DATENAME(MONTH,GETDATE())) )

--DROP THE TABLE IF IT ALREADY EXISTS
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = @table_name AND xtype = 'U')

BEGIN
EXEC('drop table ' + @table_name)
END

--CREATES TABLE FROM DYNAMIC VARIABLE AND INSERTS ROWS FROM ANOTHER TABLE
EXEC('SELECT * INTO ' + @table_name + ' FROM dbo.MASTER WHERE STATUS_CD = ''A''')

Use:

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

Declare @fs_e int, @C_Tables CURSOR, @Table varchar(50)

SET @C_Tables = CURSOR FOR
select name from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 AND name like 'TR_%'
OPEN @C_Tables
FETCH @C_Tables INTO @Table
SELECT @fs_e = sdec.fetch_Status FROM sys.dm_exec_cursors(0) as sdec where sdec.name = '@C_Tables'

WHILE ( @fs_e <> -1)
BEGIN
exec('Select * from ' + @Table)
FETCH @C_Tables INTO @Table
SELECT @fs_e = sdec.fetch_Status FROM sys.dm_exec_cursors(0) as sdec where sdec.name = '@C_Tables'
END

Also, you can use this...

DECLARE @SeqID varchar(150);
DECLARE @TableName varchar(150);
SET @TableName = (Select TableName from Table);
SET @SeqID = 'SELECT NEXT VALUE FOR ' + @TableName + '_Data'
exec (@SeqID)

You need to use the SQL Server dynamic SQL:

```
DECLARE @table NVARCHAR(128),
@sql NVARCHAR(MAX);

SET @table = N'tableName';

SET @sql = N'SELECT * FROM ' + @table;

```

Use **EXEC** to execute any SQL:

```
EXEC (@sql)
```

Use **EXEC sp_executesql** to execute any SQL:

```
EXEC sp_executesql @sql;
```

Use **EXECUTE sp_executesql** to execute any SQL:

```
EXECUTE sp_executesql @sql
```