Yet another best practice for stored procedures

As most developers know, using stored procedures is a best practice when using SQL server as the data storage for your application. It resolves issues with SQL injection and allows developers to limit access to the database to just the stored procedures, thus preventing unauthorized access.

Did you know that using BEGIN and END around the stored procedure is also a best practice? I learned that the hard way today in a rather frustrating, but not less hilarious debugging session.

How it all started

A few days ago I produced a stored procedure to retrieve a set of data for generating ASCII messages. Using yet another good practice I extended the SQL script that is responsible for creating the stored procedures as follows:

   1: CREATE PROCEDURE OriginalStoredProc

   2:     @Parameter1 int,

   3:     @Parameter2 varchar(50)

   4: AS

   5:     SELECT Some,Random,Stuff

   6:     FROM Table

   7:  

   8: IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'StoredProcName')

   9: BEGIN

  10:     DROP PROCEDURE StoredProcName

  11: END

  12:  

  13: CREATE PROCEDURE StoredProcName

  14:     @Parameter1 int,

  15:     @Parameter2 varchar(50)

  16: AS

  17: BEGIN

  18:     -- ...

  19: END

The script executed okay, so I was happy. Until I discovered about an hour later that my stored procedure was misteriously gone from the database.  I copied the snippet for my stored procedure and executed it again. However after about three hours it was gone again, frustrating the hell out of me. Of course there was no drop statement anywhere in the code of the application and my teammates also didn't drop the procedure.

Having to complete more stuff I stopped searching for the cause of this mystery and concentrated on the last part of the application I had to finish. This morning I had to do some final testing on the code I produced earlier and was confronted with my problem again. And again I could not find the cause.

The heh?!? moment

Still frustrated with the situation I asked others about my problem during this morning's the stand-up. They too had no idea wat was going on and told me to make absolutely sure I hadn't put in a drop procedure statement somewhere. With a fresh cup of coffee I started to checkout my create script as a last resort to solving the "drop procedure" puzzle. I quickly discovered what was wrong.

While creating the OriginalStoredProcedure SQL server screwed up and added the drop procedure snippet to the body of that stored procedure. Every time a teammate invoked the OriginalStoredProcedure procedure it dropped my stored procedure, causing quite a bit of trouble along the way. I felt quite stupid I can tell you that much.

Applying ducktape or other ways to fix software

While the application was still running I put the whole darn thing in pause and removed the drop procedure clause from the stored procedure that was messed up. After that I quickly added my own procedure again with the snippet I still had open. When I was done and continued to run the application it ran good again. I can't tell you how good it feels that you can be sure again that the application does what you want it to do, without presenting you with nasty surprises.

And now for the best practice: Use BEGIN and END around stored procedures, so you can be absolutely sure that you don't get side effects like I had. Also make sure that you include a GO statement after each CREATE statement. Otherwise SQL server will still include the extra code in the stored procedure and give errors telling you that the create stored procedure should be the first statement in the batch. Although it is not very likely you will get the same nasty effect like I had, it is very likely that you get some pretty weird results when extra query statements are added to the body of a stored procedure in this fashion.