Stored Procedure Documentation

Introduction

Placing remarks in strategic locations is essential for the rapid debugging and improvement of stored procedures. The urgency for documentation comes into sharper focus the more complex the stored procedure becomes. In my opinion, there are three forms of documentation with many ways to use them. Without further ado, allow me to bow and with a wave of my hand present these ways from my point of view.

Basic Documentation

Basic documentation is what you will only see when you view the stored procedure. It doesn’t need to be too fancy nor time consuming to create. I always put a header at the beginning to identify the creator, date of creation, and basic purpose of the stored procedure. Something like this will due:

/*
Created by:  Robert W Marda
When created:  10 Oct 2003
Purpose:  demonstrate documentation in a stored procedure
*/
CREATE PROCEDURE

Sometimes I include in the header information about what application calls the stored procedure. This is exceptionally useful to orient a new employee.

I don’t normally place remarks after code on the same line with that code. I think it is easier to find if it is above or below lines of code. For one query I will only place a remark above the query if needed to describe what the query is for. For multiple queries that handle a specific task I will put a line of remark before the first query and one after the last to mark the start and finish of that segment. Something like this:

--Begin parsing varchar variable.
--End parsing varchar variable.

Often I will place extra remarks near new techniques so that our other SQL Programmers will be able to understand it quicker and not necessarily have to ask me what it does.

Dual Use Documentation

What I call dual use documentation is really lines of code, usually PRINT commands, that can be seen when viewing the stored procedure but also display on execution. Most of the time I use this to display information to someone who is executing the stored procedure in Query Analyzer and has not entered required parameters. To make this work all parameters must have a default value otherwise you’ll simply get an error. Create the following stored procedure:

CREATE PROCEDURE spDocumentation1
(
@intPeopleID int,
@varLastName varchar(30),
@varFirstName varchar(30)
) AS
SELECT @intPeopleID, @varLastName, @varFirstName

Now execute the following command:

EXEC spDocumentation1

You should get an error similar to this:

Server: Msg 201, Level 16, State 4, Procedure spDocumentation1, Line 0
Procedure 'spDocumentation1' expects parameter '@intPeopleID', which was not supplied.

This is not very informative. Oh sure, it tells me I must send in a value for that parameter, but gives no clue as to what must be sent in. I can easily modify the SP call to include that parameter. Then I’ll just get an error giving me the next parameter name the SP expects.

Another way to handle this is to modify the SP like this:

ALTER PROCEDURE spDocumentation1
(
@intPeopleID int = 0,
@varLastName varchar(30) = '',
@varFirstName varchar(30) = ''
) AS
IF @intPeopleID = 0 AND @varLastName = '' AND @varFirstName = ''
BEGIN
PRINT 'spDocumentation1 expects the following parameters:'
PRINT '@intPeopleID is of datatype int and should be the ID for a person in tblPeople.'
PRINT '@varLastName is of datatype varchar and accepts up to 30 letters of a persons last name.'
PRINT '@varFirstName is of datatype varchar and accepts up to 30 letters of a persons first name.'
PRINT CHAR(10) + 'Any combination of the above three parameters will cause a search query to run.'
RETURN
END
SELECT @intPeopleID, @varLastName, @varFirstName

Now when you call the SP and fail to specify a parameter you will be given instructions on how to use the stored procedure. In addition, since this is found at the beginning of an SP it will also help anyone reviewing the code to know how to use the SP.

You’ll notice that I have tried to use descriptive parameter names to give some idea of what the parameter is used for. The first three letters I always put in lower case and they give an indication of what datatype the parameter accepts. This improves the readability of all code in an SP which is the purpose of documentation.

Simply External Documentation

This type of documentation is what you print and put in a binder for reference purposes. I believe if you have only a few SP’s and they are not too long then you can print them and add additional info as needed. However, if they are extremely long and you have hundreds of them that change every few months then I think you are simply wasting your time and a lot of paper, unless you want a printed copy of every stored procedure in case you loose all electronic versions.

I would limit this documentation to listing the stored procedures with their purpose and where they are used. It could be helpful to include a list of all their parameters. I would group them according to database and purpose within that database.

To be honest, this is the type of documentation we do the worst in creating and so I do not have as clear a picture of what will be useful or not. I think what I mentioned could be a good beginning.

Conclusion

Please accept my humble attempt to explain useful techniques on how to improve the readability of any code you create as a stored procedure. Hopefully, you can see the usefulness of what I have presented. If you don’t see it now, you will in 6 months to a year when you go back to a stored procedure you developed back then and curse yourself for having to figure out what you did simply because you didn’t spend a little extra time to include some simple remarks.