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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The parens are important here:

EXEC (@SQL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.