SQL Using a var as a table name

Question

I have this piece of code and instead of repeating the table name in each field every time I run the query, I just want to set the table name to a variable and use that, the code I have doesn't work, can anyone point me in the right direction?

 DECLARE @table_name varchar(20)
 SET @table_name = 'test_table'

 IF object_id(@tableName) is not null --    object_id('TABLE_NAME')
 PRINT 'Table Exists'
ELSE
    CREATE TABLE [@tableName]( --TABLE DOES NOT EXIST, THIS WILL EXECUTE
    [ContactID_ContactID] [tinyint] NOT NULL,
[OrganisationID_OrganisationID] [int] NOT NULL,
[Note] [nchar](300) NULL
)

This just looks for if the table exists and if it doesnt, it will create the table

@Rokuto i have read a few of them but none give the solution to what i need — kieron oates
– kieron oates, Commented Jul 10, 2017 at 8:46
Because you need to adapt them to your problems :) Or just read answers. — Rokuto
– Rokuto, Commented Jul 10, 2017 at 8:47

Pரதீப் · Accepted Answer · 2017-07-10 08:46:29Z

3

You need dynamic query for getting table name from variable

DECLARE @table_name varchar(20)
 SET @table_name = 'test_table'

 IF object_id(@tableName) is not null --    object_id('TABLE_NAME')
 PRINT 'Table Exists'
ELSE
    exec('CREATE TABLE ['+@tableName+']( --TABLE DOES NOT EXIST, THIS WILL EXECUTE
    [ContactID_ContactID] [tinyint] NOT NULL,
[OrganisationID_OrganisationID] [int] NOT NULL,
[Note] [nchar](300) NULL
)')

answered Jul 10, 2017 at 8:46

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evaldas Buinauskas · Accepted Answer · 2017-07-10 08:47:00Z

DECLARE @tableName sysname = N'test_table';
DECLARE @query NVARCHAR(1000);

IF OBJECT_ID(@tableName, 'U') IS NOT NULL --    object_id('TABLE_NAME')
    PRINT 'Table Exists';
ELSE
BEGIN
    --TABLE DOES NOT EXIST, THIS WILL EXECUTE
    SET @query = N'
    CREATE TABLE ' + QUOTENAME(@tableName) + N' (
        [ContactID_ContactID] [tinyint] NOT NULL,
        [OrganisationID_OrganisationID] [int] NOT NULL,
        [Note] [nchar](300) NULL);';

    EXECUTE sp_executesql @query;
END;

You must use dynamic SQL in order to do that. Also note that I've slightly changed these:

@tableName variable type to sysname.
If condition to check for 'U' (tables only)
QUOTENAME()'d your table variable name, SQL Server will the ensure that it's a valid name and will prevent SQL Injection possibility.

dbajtr · Accepted Answer · 2017-07-10 08:48:48Z

1

Dynamic SQL is what you need, something like:

 DECLARE @table_name varchar(20) = 'test_table'


 IF object_id(@table_name) is not null --    object_id('TABLE_NAME')
 PRINT 'Table Exists'
ELSE

exec(
'CREATE TABLE ' + @table_name +' (
[ContactID_ContactID] [tinyint] NOT NULL,
[OrganisationID_OrganisationID] [int] NOT NULL,
[Note] [nchar](300) NULL
)')

answered Jul 10, 2017 at 8:48

dbajtr

2,0442 gold badges16 silver badges23 bronze badges

Comments

Esteban P. · Accepted Answer · 2017-07-10 08:49:15Z

1

my solution would look like:

 DECLARE @table_name varchar(20)
 DECLARE @sql_statement varchar(5000)
 SET @table_name = 'test_table'

 IF object_id(@table_name) is not null --    object_id('TABLE_NAME')
 BEGIN
    PRINT 'Table Exists'
 END
 ELSE
 BEGIN
    SET @sql_statement = 'CREATE TABLE ' + @table_name + ' 
                         ( 
                            [ContactID_ContactID] [tinyint] NOT NULL,
                            [OrganisationID_OrganisationID] [int] NOT NULL,
                            [Note] [nchar](300) NULL
                          )'
    EXEC (@sql_statement)
 END

answered Jul 10, 2017 at 8:49

Esteban P.

2,8203 gold badges31 silver badges45 bronze badges

Comments

Abdullah Danyal · Accepted Answer · 2017-07-10 08:49:22Z

0

You have to generate the SQL dynamically for that. Use sp_executesql

The following article will be helpful: http://www.sommarskog.se/dynamic_sql.html

answered Jul 10, 2017 at 8:49

Abdullah Danyal

1,1661 gold badge10 silver badges26 bronze badges

Comments

Prabhat G · Accepted Answer · 2017-07-10 08:53:51Z

0

Dynamic SQL is the thing you're looking for :

Syntax is :

DECLARE @SQLString NVARCHAR(MAX);
SET @SQLString = '<query>'
EXEC (@SQLString);

hence,

DECLARE @tableName varchar(20);
SET @tableName = 'test_tbl2';
DECLARE @SQLString NVARCHAR(MAX);


IF object_id(@tableName) is not null
PRINT 'Table Exists'
ELSE
SET @SQLString = 'CREATE TABLE ['+@tableName+']( 
                        [ContactID_ContactID] [tinyint] NOT NULL,
                        [OrganisationID_OrganisationID] [int] NOT NULL,
                        [Note] [nchar](300) NULL
                    )'
EXEC (@SQLString)

answered Jul 10, 2017 at 8:53

Prabhat G

3,0492 gold badges27 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL Using a var as a table name

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related