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

Actually Fiddel don't changes its URL till any thing done without any error. — Romesh
– Romesh, Commented Sep 6, 2013 at 7:25

juergen d · Accepted Answer · 2013-09-06 06:38:44Z

5

Add parentheses around your variable when executing

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

SQLFiddle demo

answered Sep 6, 2013 at 6:38

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

dcaswell Over a year ago

Excellent answer. There's some additional info here: technet.microsoft.com/en-us/library/aa175921%28v=sql.80%29.aspx

Community · Accepted Answer · 2017-04-13 12:42:39Z

1

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:

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Sep 6, 2013 at 6:38

roman

118k30 gold badges205 silver badges209 bronze badges

Comments

Raj · Accepted Answer · 2013-09-06 06:39:45Z

0

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

That should do the trick

Raj

answered Sep 6, 2013 at 6:39

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Collectives™ on Stack Overflow

Msg 2812 : "Create Table Using Dynamic Script"

3 Answers 3

SQLFiddle demo

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related