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

But watch out for single quotes within your Stored Procedure - they need to be "escaped" by adding a second one. The first answer has done this, but just in case you missed it. A trap for young players.

Answer 3

Versioning your database is the way to go, but... Why conditionally create stored procedures. For Views, stored procedures, functions, just conditionally drop them and re-create them every time. If you conditionally create, then you will not clean-up databases that have a problem or a hack that got put in 2 years ago by another developer (you or I would never do this) who was sure he would remember to remove the one time emergency update.

Answer 4

I must admit, I would normally agree with @Peter - I conditionally drop and then unconditionally recreate every time. I've been caught out too many times in the past when trying to second-guess the schema differences between databases, with or without any form of version control.

Having said that, your own suggestion @Josh is pretty cool. Certainly interesting. :-)

Answer 5

Problem with dropping and creating is you lose any security grants that had previously been applied to the object being dropped.

Answer 6

IF NOT EXISTS(SELECT * FROM sys.procedures WHERE name = 'pr_MyStoredProc')

BEGIN

CREATE PROCEDURE pr_MyStoredProc AS
SET NOCOUNT ON

END

ALTER PROC pr_MyStoredProc AS

SELECT * FROM tb_MyTable

Answer 7

use the 'Exists' command in T-SQL to see if the stored proc exists. If it does, use 'Alter', else use 'Create'