CODEWITHSUNDEEP
sqlsql-servert-sql
kieron oates
kieron oates

Reputation: 115

SQL Using a var as a table name

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

Upvotes: 0

Views: 196

Answers (6)

Prabhat G
Prabhat G

Reputation: 3029

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)

Upvotes: 0

Abdullah Danyal
Abdullah Danyal

Reputation: 1146

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

Upvotes: 0

Esteban P.
Esteban P.

Reputation: 2809

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

Upvotes: 1

dbajtr
dbajtr

Reputation: 2044

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

Upvotes: 1

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

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

Upvotes: 3

Related Questions