[SOLVED] T-SQL Create Table with Dynamically Named Database with sp

T-SQL Create Table with Dynamically Named Database with sp_executesql

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?

Solution

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