asked on

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!

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ttist25

ASKER

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!!!!!!!!!!!!!!!

Scott Pletcher

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