Msg 2812 : "Create Table Using Dynamic Script"

Question

I wanted to create a table using dynamic SQL.

If I creates a table using

CREATE Table TodayTemp(id varchar(20))
DROP TABLE TodayTemp

Then there is no problem. It works fine. But problem using this is I can't create columns dynamically. Hence I tried using store create script in a variable and then finally execute them using EXEC command.

Like

Declare @CreateTableCmd varchar(max)
SET @CreateTableCmd = 'CREATE Table TodayTemp(id varchar(20))'
Exec @CreateTableCmd

But this causes an error

Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'CREATE Table TodayTemp(id varchar(20))'.

Answer 1

Add parentheses around your variable when executing

Declare @CreateTableCmd varchar(max)
SET @CreateTableCmd = 'CREATE Table TodayTemp (id varchar(20))'
Exec (@CreateTableCmd)
     ^---------------^--------here

SQLFiddle demo

Answer 2

if you want to exec your script with exec, call it like this:

Exec (@CreateTableCmd)

Another way to do this is to use sp_executesql stored procedure:

exec sp_executesql @stmt = @CreateTableCmd

there're many links comparing this two approaches:

• https://dba.stackexchange.com/questions/4559/difference-between-exec-and-sp-executesql-with-no-parameters
• http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/

Answer 3

Declare @CreateTableCmd varchar(max)
SET @CreateTableCmd = 'CREATE Table TodayTemp'
Exec (@CreateTableCmd)

That should do the trick

Raj