A table name as a 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  quot  tablename quot    What s the right way to have the table name populated dynamically

User · Answer

Declare   tablename varchar 50   set  tablename    Your table Name   EXEC  select   from      tablename

User · Answer

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

User · Answer

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 varchar 50  declare  table varchar 50  declare  query nvarchar 500   set  schema    dbo  set  table    ACTY   set  query    SELECT   FROM  DB ONE        schema            table      EXCEPT SELECT   FROM  DB TWO        schema            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

User · Answer

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  lt  gt  -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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  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

User · Answer

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

[sql] A table name as a variable

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to variable-names

Examples related to tablename