MSSQL Server 2012 - ALTER TABLE Using Dynamic SQL

Hey there,

I'm trying to add a column to a table using dynamic SQL but I'm getting the following error:
Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'ALTER TABLE MyTable_20180115
ADD MyNewColumn bit'.


My code looks like this:
DECLARE @TABLENAME VARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)
SET @TABLENAME = 'MyTableName'+(CONVERT(varchar(8),getdate(),112))
SET @SQL = 'ALTER TABLE '+ @TABLENAME +' ADD MyNewColumn bit'
EXEC @SQL

I think I'm missing something - any ideas?  

Thanks!
LVL 1
ttist25Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The parens are important here:

EXEC (@SQL)
0
 
ttist25Author Commented:
Woah - who knew???  
Scott saves the day again!

Why does EXEC @SQL work on other dynamic SQL statements in my script but not with the ALTER TABLE statement?

THANKS!!!!!!!!!!!!!!!
0
 
Scott PletcherSenior DBACommented:
By default, a new script starting in SQL has an "implied" EXEC in front of it (if it doesn't match another command pattern that SQL recognizes).

For example, try this:
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'master.sys.sp_databases'
EXEC @sql
that's a stored proc, but it should run just fine because of the implied "EXEC".

SQL basically did the same thing with your SQL, adding the implied "EXEC" to:
ALTER TABLE MyTable_20180115
ADD MyNewColumn bit'
That's why you get the message "Could not find stored procedure..."

If you want to run two commands, you must supply the EXEC yourself for all commands.  Yet another quirk of SQL Server :-).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.