Home Table name as variable
Reply: 7

Table name as variable

SoftwareGeek
1#
SoftwareGeek Published in 2010-05-15 01:07:17Z

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?

mdma
2#
mdma Reply to 2010-05-15 01:14:49Z

Table names and column names need to be static, if the query is static. For dynamic table or column names, you should generate the full SQL dynamically, and use sp_executesql to execute it.

More details here: The curse and blessings of dynamic SQL

Adi Inbar
3#
Adi Inbar Reply to 2013-09-18 18:40:38Z

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 DB 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''')
dcp
4#
dcp Reply to 2010-05-15 01:13:25Z

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)
eddie_cat
5#
eddie_cat Reply to 2015-07-14 18:32:30Z

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
t-clausen.dk
6#
t-clausen.dk Reply to 2011-09-06 09:11:49Z

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)
Dadep
7#
Dadep Reply to 2017-07-25 12:30:07Z
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

I've put the lines that p

Laurent
8#
Laurent Reply to 2016-08-10 07:52:35Z
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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.385778 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO