T-SQL Create Table with Dynamically Named Database with sp_executesql

Question

I am attempting to create a table in T-SQL using sp_executesql. The name of the database containing the table is dynamic.

DECLARE @ID int = 1031460
DECLARE @TableName nvarchar(max) = '[MyDatabase' + CAST(@ID as nvarchar(10)) + '].[dbo].[MyTable]'

DECLARE @CreateTable nvarchar(max) = N''
SELECT @CreateTable = 
N'
CREATE TABLE @TableName
(
    ID int
)
'

EXECUTE sp_executeSQL @CreateTable, N'@TableName nvarchar(max)', @TableName = @TableName

This script results in this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@TableName'.

What is the best way to specify the name of the table to create dynamically based on parameters of sp_executeSQL?

Hadi · Accepted Answer · 2022-02-11 17:55:43Z

3

You cannot pass Tablename as a parameter even we using sp_executesql procedure. You have to build you own dynamic sql query

SELECT @CreateTable = 
N'
CREATE TABLE ' + @TableName + '
(
ID int
)
'
Exec sp_executesql @CreateTable

edited Feb 11, 2022 at 17:55

answered Dec 14, 2016 at 20:54

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

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

1 Comment

Jacob Quisenberry Over a year ago

I suppose that is what this answer means: stackoverflow.com/a/2838507/814438

M.Ali · Accepted Answer · 2016-12-14 21:05:47Z

When you are passing the table name as a parameter to sp_executesql, it treats it as a literal string, to make it treat it as an object name try something like this......

DECLARE @ID int = 1031460
DECLARE @TableName nvarchar(max) = QUOTENAME('MyDatabase' + CAST(@ID as nvarchar(10))) 
                                   + '.[dbo].[MyTable]'

DECLARE @CreateTable nvarchar(max);

SET @CreateTable = N' CREATE TABLE ' + @TableName
                 + N' (
                        ID int
                      )'

Exec sp_executesql @CreateTable

EDIT

It is good practice to check if an object already exists before you try to create it.

You can check if the object already exists and drop it and then create the new one, you code should look something like.....

DECLARE @ID int = 1031460
DECLARE @TableName nvarchar(max) = QUOTENAME('MyDatabase' + CAST(@ID as nvarchar(10))) 
                                   + '.[dbo].[MyTable]'

DECLARE @CreateTable nvarchar(max), @DropTable nvarchar(max);


-- Drop table if already exists 
SET @DropTable = N' IF OBJECT_ID('''+ @TableName +''') IS NOT NULL '
               + N' DROP TABLE ' + @TableName

Exec sp_executesql @DropTable 

SET @CreateTable = N' CREATE TABLE ' + @TableName
                 + N' (
                        ID int
                      )'

Exec sp_executesql @CreateTable

Collectives™ on Stack Overflow

T-SQL Create Table with Dynamically Named Database with sp_executesql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related