CODEWITHSUNDEEP
sqlsql-servert-sqlvariable-namestablename
SoftwareGeek
SoftwareGeek

Reputation: 15772

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?

Upvotes: 236

Views: 610740

Answers (11)

Mohamad Idzhar
Mohamad Idzhar

Reputation: 1

Hopefully this code helps

-- Start process check by counting rows item
SET @Count_Row = 0;
SET @SqlMain = 'SELECT @Cnt = COUNT(*) FROM ['+@Tab_Name+'] WHERE ['+@Col_Name+'] LIKE ''%'+@SearchVal+'%'''
SET @Parm = '@Cnt INT OUTPUT'

EXEC sp_executesql @SqlMain , @Parm , @Cnt = @Count_Row OUT

SET @TotalRow = @TotalRow + @Count_Row

IF  @Count_Row >= 1

    PRINT @Tab_Name+' - '+@Col_Name+' - '+ CONVERT(VARCHAR(10), @Count_Row)

FETCH NEXT FROM CUR_GET_INFO INTO @Tab_Name,@Col_Name

END

Upvotes: -1

mdma
mdma

Reputation: 57707

For static queries, like the one in your question, table names and column names need to be static.

For dynamic queries, you should generate the full SQL dynamically, and use sp_executesql to execute it.

Here is an example of a script used to compare data between the same tables of different databases:

Static query:

SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]

Since I want to easily change the name of table and schema, I have created this dynamic query:

declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);

set @schema = 'dbo'
set @table = 'ACTY'

set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
EXCEPT
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);

EXEC sp_executesql @query

Since dynamic queries have many details that need to be considered and they are hard to maintain, I recommend that you read: The curse and blessings of dynamic SQL

Upvotes: 185

Reza Jenabi
Reza Jenabi

Reputation: 4279

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

Upvotes: 2

Disha Sojitra
Disha Sojitra

Reputation: 7

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)

Upvotes: -2

Laurent
Laurent

Reputation: 1

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

Upvotes: -3

Atul Chaudhary
Atul Chaudhary

Reputation: 1133

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

Upvotes: 46

Tim
Tim

Reputation: 1427

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''')

Upvotes: 131

Paul Kearney - pk
Paul Kearney - pk

Reputation: 5533

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)

Upvotes: 17

dcp
dcp

Reputation: 55434

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)

Upvotes: 40

user13581111
user13581111

Reputation: 35

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

Upvotes: 0

ghgh
ghgh

Reputation: 109

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

Upvotes: 9

Related Questions