Sunday, November 18, 2012 2:08 PMAs part of my integration strategy, I have a few SQL scripts that run in order to update the database. The first thing all of these scripts do is check to see if they need to run, e.g.:
if @version <> @expects
declare @error varchar(100);
set @error = 'Invalid version. Your version is ' + convert(varchar, @version) + '. This script expects version ' + convert(varchar, @expects) + '.';
raiserror(@error, 10, 1);
...sql statements here...
Works great! Except if I need to add a stored procedure. The "create proc" command must be the only command in a batch of sql commands. Putting a "create proc" in my IF statement causes this error:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Ouch! How do I put the CREATE PROC command in my script, and have it only execute if it needs to?
Sunday, November 18, 2012 4:35 PM
As the message say, the CREATE command must be the first in a batch. You could use a kind of dynamical SQL like
EXEC ('CREATE PROCEDURE ...')
Olaf HelperBlog Xing
- Proposed As Answer by Naomi NMicrosoft Community Contributor Sunday, November 18, 2012 7:49 PM
Sunday, November 18, 2012 4:58 PMModerator
Please, could you give more informations about your problem ?
- the minimum version of your SQL Server ( 2000,2005,2008,2008 R2,2012...)
- i am not sure , but i think that i have identified the language used in your code. I think it is Transact-SQL ( i hope it is not DMO + vbscriptVBA, because , in this case, you will be in difficulty : DMO is finishing its life and the only alone forum for that is the SMO/DMO forum, but i think that in the 2 last years, no thread about DMO has been created or answered )
You should have a look at this link :
If it is a T-SQL script, a moderator will move your thread towards the t-sql forum ( in my case, i prefer to wait for the agreement of the original poster before moving a thread )
Please, could you explain what you mean by
" Putting a "create proc" in my IF statement causes this error:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch" ?
Possible solutions :
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
- Edited by Papy NormandModerator Sunday, November 18, 2012 5:16 PM i clicked on submit before writing my full post
Monday, November 19, 2012 12:25 AM
CREATE/ALTER PROCEDURE' must be the first statement in a query batch
We can add a GO Command before each Create/Alter PROC lines in your script. GO command Signals the end of a batch of Transact-SQL statements .
- Edited by Johnson T A Monday, November 19, 2012 12:26 AM
Monday, November 19, 2012 7:59 AMModerator
Wrap it in an EXEC(), like so:
if @version <> @expects
exec('CREATE PROC MyProc AS SELECT ''Victory!''');
TechNet Community Support
- Marked As Answer by Iric WenModerator Tuesday, November 27, 2012 9:29 AM