How do I conditionally create a stored procedure in SQL Server?

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

Here's what I came up with:

Wrap it in an EXEC(), like so:

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

Works like a charm!

Answer 2

SET NOEXEC ON is good way to switch off some part of code

IF NOT EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions')   SET NOEXEC ON GO CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',') RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Granite.SQL.CLR.Functions].CLR_CharList_Split GO SET NOEXEC OFF 

Found here: https://codereview.stackexchange.com/questions/10490/conditional-create-must-be-the-only-statement-in-the-batch

P.S. Another way is SET PARSEONLY { ON | OFF }.