create procedure in SQL Server problem

Question

As 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
    begin
    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);
    end
else
    begin
        ...sql statements here...
    end
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?

Answer 1

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 Helper

Blog Xing

Answer 2

Hello,

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 :

http://msdn.microsoft.com/en-us/library/ms187926(v=sql.105).aspx

http://msftdbprodsamples.codeplex.com/SourceControl/changeset/view/96197#356870 

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" ?

http://msdn.microsoft.com/en-us/library/ms187471(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms182717(v=sql.105).aspx

Possible solutions :

http://beyondrelational.com/modules/2/blogs/70/posts/10926/createalter-procedure-in-a-single-batch.aspx

http://stackoverflow.com/questions/9051006/sql-errorcreate-alter-procedure-must-be-the-first-statement-in-a-query-batch

http://ask.sqlservercentral.com/questions/5108/database-use-issues-with-dynamic-sql.html

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

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 .

Answer 4

Hi Vayne,

Wrap it in an EXEC(), like so:

if @version <> @expects
    begin
        ...snip...
    end
else
    begin
        exec('CREATE PROC MyProc AS SELECT ''Victory!''');
    end

---

Iric Wen

TechNet Community Support