A better way to update SQL stored procedures

A common pattern to manage SQL stored procedures is to drop the current procedure and recreate it. Unfortunately, this doesn’t work if you’re trying to run a high-availability service. Here’s the (broken) drop+create pattern:

-- Delete the stored procedure if it already exists
if exists (
  select * from sys.objects
    where name = N'p_MyProc'
    and type = N'P'
) begin
  drop procedure p_MyProc
end
go

-- Now create it again
create procedure p_MyProc as begin
  print 'go forth and do great things'
end
go

-- And set permissions
grant execute on p_MyProc to SomeRole
go

There’s nothing wrong with this code. Just kick all your users off the server, switch to single user mode, execute the code above and it will work fine.

Oh, what’s that? You’re running a service that needs to be highly available, and you can’t take a maintenance period every time you want to change code?

Using the drop+create method there is obviously a small window of time when the stored procedure does not exist. And as we know from examples like the Seattle monorail crash (see notes at the end of this article), any system that is designed with a built-in flaw will eventually fail because of that flaw.

Here is a better solution:

-- if the procedure does not exist create a placeholder
if not exists (
  select * from sys.objects
    where name = N'p_MyProc'
    and type = N'P'
) begin
  exec('create procedure p_MyProc as
    RAISERROR (''MyProc not defined'', 16, 1);');
end
go

grant execute on p_MyProc to SomeRole
go

-- update stored proc
alter procedure p_MyProc as begin
  print 'go forth and do great things'
end
go

NOTE: Thanks to Justin Wignall for bug-fix to the code above.

This ensures that the stored procedure always exists, and (because SQL is transactional) it is possible to have one caller finishing a call to the old version of the stored procedure while the new version is added and called.

I’ve used this trick successfully for the development of Guild Wars using SQL Server 2000, and later SQL Server 2005, and have not encountered any problems using this technique, even on servers running sustained load of 3000+ transactions per second. In fact the standard operating procedure for our database updates was to update all stored procedures (several hundred of them) every time we performed a deployment.

I assume that similar tricks will work for MySQL and PostgreSQL, and would love to hear from users of those platforms about their experiences.

Update (11/5/2011):

It turns out that MySQL doesn’t support atomic updates of stored procedures; apparently this is a long-standing bug, first filed in 2005 and still not fixed (https://bugs.mysql.com/bug.php?id=9588). And PostgreSQL and Oracle both do properly support this feature with a different SQL syntax: “CREATE OR REPLACE PROCEDURE”.

Notes:

In case you’re wondering about the reference to the Seattle monorail crash, which is a great example of design failure, here’s a bit more information. The original Seattle monorail was built for 1962 World’s Fair, and had two sets of tracks so that two trains could operate side-by-side. The tracks were shortened in the 1988 to end at the newly constructed Westlake Center mall. The new design allowed for an automatic passenger-loading ramp to extend to the trains, but led to the tracks being too close together for two trains to be there at the same time. As you might imagine, this design eventually failed, though it took seventeen years https://www.tecnetinc.com/monorail.html. Of course, when you’re running several thousand SQL transactions per second, such failures are all the more likely.