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?

Answer 1

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

Answer 2

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