A Good Stored Procedure Template

Most database developers don’t spend much time recreating the wheel every time they have to create a stored procedure. I, for one, like to keep a template handy to get me started each time I need to begin writing a new proc. The following script is an excellent start. Not only does it include all the necessary header calls, though you’re requirements may vary, it also includes a comment block to record the author, date of publication and the all important change log. Additionally you will see a spot for a sample execution line, which is generally welcomed by your DBA or whoever will be publishing your procs.

In the above template I have expanded upon the default template’s header SET commands to include a variety of useful options which help the procedure in regards to indexes and computed columns.

The major benefit to this template is the built in error handling when dealing with transactions.  As long as the transaction was initiated within the procedure itself, this template will allow the proc to commit or rollback the transaction.  Though this template may be overkill if you’re simply writing a report and no transactions are being called, you can still utilize this template for any stored procedure that is being written.

You will also notice that I have included a line of code to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This specifies that the procedure can read rows that have been modified by other transactions but that have not been committed. This will allow for more accurate data to be returned by the proc but is not always necessary.  Depending on the function of your procedure you may want to include this.

I have also included a template for tracking changes to the procedures.  Depending on how you track changes you may want to modify this.  In my organization we track the date and time, who made the change, as well as a brief description of the change.  The Change Code is used for easily locating the change in a large procedures.  We typically use a combination of the user’s initials along with the date to designate a change code, which would also be placed next to the modified portion of the procedure as a comment.  An example of this is shown below.

change_log_screenshot

An additional piece I like to put under the description are the performance metrics of the procedure.  This allows me to quickly and easily reference previous performance standards for each procedure to determine if the changes that were made are causing it to perform poorly.  If you would like to know more about calculating performance metrics see my post on Reviewing Performance Metrics on Stored Procedures in SQL Server.