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 object_id = OBJECT_ID(N'p_MyProc')
  and type = N'P'
) then
  drop procedure p_MyProc
end

-- Now create it again
create procedure p_MyProc as begin
  -- awesome code here
end

-- And set permissions
grant execute on p_MyProc to SomeRole

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 stored procedure does not exist then create a placeholder
if not exists (
  select * from sys.objects where object_id = OBJECT_ID(N' p_MyProc')
  and type = N'P'
) then
  create procedure p_MyProc as RAISERROR ('MyProc not defined', 16, 1);
  grant execute on p_MyProc to SomeRole
end

-- update stored proc
alter procedure p_MyProc as begin
  -- awesome code here
end

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 (http://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 (http://seattletimes.nwsource.com/html/localnews/2002650818_monorail28m.html). Of course, when you’re running several thousand SQL transactions per second, such failures are all the more likely.