SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Documenting Stored Procedures


Documenting Stored Procedures

Author
Message
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 113
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/documentingstoredprocedures.asp

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Philip Kelley
Philip Kelley
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 232
One thing I always try to do is add a comment at the start of a "programming block" -- BEGIN, IF, ELSE, WHILE. It really helps when reviewing code to have a quick comment on what's going on in this IF block, or what occurs in each iteration of the WHILE loop.

Here's an outline. (Please note that I do indent my code; I've yet to figure out how to get this forum to accept leading spaces on new paragraphs.)

IF <someCondition>
BEGIN
-- Description of conditions causing this IF code to run

END
ELSE
BEGIN
-- Description of conditions causing this ELSE code to run (very useful if the IF clause is fifty lines back)
END
ENDIF




ensslen
ensslen
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 1
If you're going to be generating external documentation (which some internal/external clients like) then the data dictionary can be used to great effect to streamline your process.

Even a relatively simple SELECT statement against SYSCOMMENTS can extract the "Basic Comment" at the begining of every procedure. You could then XML->XSL that output to word as HTML or use PRINT statements to output it to word as cut and paste.

Generally though, I feel that such documentation is not worth any effort, even as rapid a process as this one.

Steven



Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147785 Visits: 19441
Nice summary of items. Personally I usually set parameters to NULL and then check for NULL to display the "you need parameter x" included" message.

I'm wary of external documentation. People get too busy and this is difficult to keep up with, so I don't recommend it. Instead, I prefer to keep any notes, docs, etc. in the proc. Still searching for a good way to provide this to new people, but I think it needs to be kept with the code.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
www.dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
rawheiser
rawheiser
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 13
Its not exactly 'documentation' but ...

Don't forget the ability of VSS or your source control system to use keywords to keep the basic date/time last changed info up to date.

As someone who moves procs around, having this header helps me quickly see what is in production and in test (see below for my basic header).

/*
---------------------------------------------
$Logfile: $
$Revision: $
$Date: $
---------------------------------------------
*/

As far as internal/external documentation ... the internal stuff helps you understand the actual functioning of the object, but good external documentation helps you understand the overall system.

And don't forget consistant standards in naming objects and variables. That assists better than volumes of text in "getting it".



SQLBill
SQLBill
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11737 Visits: 1085
I liked the article, especially the description of how to put in the informative directions using the PRINT command. I'm going to go through my sp's and see where I can use that.

-SQLBill

*If you're not learning something every day - you are asleep or just dead.



jpipes
jpipes
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 Visits: 1
Good article, but I have only one disagreement. Instead of PRINT statements, I prefer to use RAISERROR. With RAISERROR, you can pass in variables to make the error message descriptive, plus it actually produces an error, which can be caught and rethrown by the calling procedure if necessary. Example:

CREATE PROC MyProc1
@LastName VARCHAR(30) = ''
, @FirstName VARCHAR(30) = ''
AS
IF @LastName = '' OR @FirstName = '' BEGIN
RAISERROR('MyProc1 expects both parameters @FirstName and @LastName. Both are VARCHAR(30). Values supplied were @LastName: %s and @FirstName: %s', 16, 1, @LastName, @FirstName)
END
...

Just my 2 cents.



merceroz
merceroz
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 1

I prefer to add my comments after the create procedure, eg

create proc rpcNameOfProc
@AcctMonthEndDate datetime
as

--Custom script created for Client
--This script should be reinstalled and tested at time of an upgrade.
--Name - rpcNameOfProc
--Version - 4
--Author - Gareth Mercer
--Create Date - 20 October 2004
--Description - Used for the report Report.rpt

That way when you're at a client's and you want to know what version they have, you can do an
sp_helptext, rather than rely on the client to have a record.





leslaw
leslaw
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2713 Visits: 36

Your's example code:

It's better to use statements like :

if @parameter is null ....

SP parameters can be obtained from metadirectory

Uneeded and espesially not current comments are wost then none!!!


Julian Kuiters
Julian Kuiters
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1269 Visits: 1

RAISERROR is the way to go if you are going to throw an error up the chain.

At the very least if you are using RETURN you should be giving RETURN a value so that your calling EXEC knows there was a failure.

CREATE PROC dbo.myBad
@myInt int = 0
AS
IF @myInt = 0
RETURN 1 --- @myInt can't be 0
GO
DECLARE @myResult int
EXEC @myResult = dbo.myBad
IF @myResult <> 0
BEGIN -- Error handling routine
PRINT 'There was an error'
.....
END






Julian Kuiters
juliankuiters.id.au
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search