views:

2423

answers:

7

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?

+7  A: 

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!

Josh Hinman
Re-creating procs every time (conditionally dropping) is a much better solution. If use exec, you gain nothing and there are downsides; your proc has to escape strings and any line numbers in errors will refer to the line number relative to the exec command.
Peter
+1 for being able to handle conditions other than existence (in my case @@Version drives whether certain procs are created or not)
cmsjr
+1 - I used this to configure my CRUD generator to create a stub procedure if it did not already exist, and _then_ alter it. This allows me to amend my stored procs, build the CRUD but _preserve any permissions assigned against existing procs_.
Neil Moss
+2  A: 

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.

Anthony K
+1  A: 

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.

Peter
+1  A: 

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. :-)

robsoft
A: 

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

A: 

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

A: 

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

Jobo