Stored Procedure Documentation

Question

Post reply

Stored Procedure Documentation

SQL Espo

SSCrazy

Points: 2678
More actions
December 6, 2019 at 1:30 pm

#3705362

I have developed several stored procedures that will be utilized by developers on a project (surprise right?). My question is that I need to provide them with documentation about how the stored procedure works (including parameters and results). Does anyone have a "simple" template for creating this documentation? I do not want to make it so complicated that no one will understand it but want to make sure I cover all bases. I really have not had to do this in a very long time, and at my last position, I was provided a "template" to use.
Thanks a lot in advance!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

I'm not sure how far I'd go beyond:

Name

This is what it does, in short, clear, descriptive phrases

These are the parameters

These parameters are required

These parameters have default values and here they are

These are the return parameters (if any)

Maybe the result set? That's about it. Short & clear is usually best for documentation in my opinion.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, and a history of the changes that have been made (date, person, description of change).

John

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

John Mitchell-245523 wrote:

Yes, and a history of the changes that have been made (date, person, description of change).
John

Oh, I hate disagreeing with people, but

NOOOOOOO!!!!!

Don't do this. Your procs (and all your database code, from CREATE TABLE on up) should be in source control. That's where this type of information is kept. Not in comments in the procedure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

The flower box in code is absolutely essential and just about the only place where documentation won't accidentally get lost. Someone would have to make an effort to delete it. I'd place no maximum limits there or on comments in the code.

See the code in the following article for the way I actually write a flower box in the code. It contains the basic purpose, usage, programmer notes, and revision history. If it has dependencies, I'll sometimes include those depending on what it is. There are some places where you shouldn't have to document essential tribal knowledge in every piece of code you write.

https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

More than in the flower box, especially for stored procedures, I believe that if you cannot write a simple comment to explain what each and every SELECT (or whatever) does, then you've not done it right. I worked at a company where all of the stored procedures and other code objects had absolutely zero comments. On a large, complex stored procedure, it would take someone two days to do all of the research necessary to make a rather simple modification and then they'd get the modification wrong and it would come back from QA as broken. I told folks to start writing comments in all new code and any piece of legacy code they touched. It didn't actually slow things down much because all they had to do was write a simple comment after they figured out was a "segment" of code was doing.

The benefits were immediate on the code that was quickly documented. After two years, all of the code had been documented. Research time dropped from around 1-2 days to about 10-60 minutes and we came damned close to having a zero defect environment, which also allowed us to push out code faster than ever because rework after a QA failure (or, worse, a production failure) takes 8 times longer to find and fix the problem than it does to do it right the first time every time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Oh yeah... almost forgot... a "Coding'n'Formatting" standard goes a hell of a long way when it comes to readability during troublshooting or modifications. I do 100% peer reviews for the folks I work for. The code could be functionally brilliant code that works perfectly but, if it fails formatting/readability, I reject it without exception. Comments are simple to include and with all the code prettifiers out there (we have SQL Prompt just to drop a RedGate product name), there is absolutely no excuse for shoddy code. Just freakin' doit! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Jeff Moden wrote:

there is absolutely no excuse for shoddy code. Just freakin' doit! 😀

Words to live by.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 7

As in most development the code should speak for it self, and you should use:

Source control (containing change documentation)
Formatting tool/formatting rules
Good and describing naming rules

Your procedure should have a header containing, a short & clear summary, description of parameters/default values/result set. Last in the header you should have links/tag(s) to your CMS/test tool/external documentation or a add the documentation directly. Complex code parts can have short local description. Simple code parts should never have documentation.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

jonas.gunnarsson 52434 wrote:

As in most development the code should speak for it self, and you should use:

<li style="list-style-type: none;">
Source control (containing change documentation)

<li style="list-style-type: none;">
Formatting tool/formatting rules

<li style="list-style-type: none;">
Good and describing naming rules

Your procedure should have a header containing, a short & clear summary, description of parameters/default values/result set. Last in the header you should have links/tag(s) to your CMS/test tool/external documentation or a add the documentation directly. Complex code parts can have short local description. Simple code parts should never have documentation.

You were doing great up until that last sentence, which I totally disagree with. You should never have to read code to determine its basic intent. Of course, even though that has worked very well where ever I've worked, that's just my opinion, but I had to say it out loud.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Neil Burton SSC-Insane Points: 22905 More actions · Answer 9

Jeff Moden wrote:

jonas.gunnarsson 52434 wrote:
As in most development the code should speak for it self, and you should use:

<li style="list-style-type: none;">
<li style="list-style-type: none;">

<li style="list-style-type: none;">
<li style="list-style-type: none;">
Source control (containing change documentation)

<li style="list-style-type: none;">
<li style="list-style-type: none;">

<li style="list-style-type: none;">
<li style="list-style-type: none;">
Formatting tool/formatting rules

<li style="list-style-type: none;">
<li style="list-style-type: none;">

<li style="list-style-type: none;">
<li style="list-style-type: none;">
Good and describing naming rules

Your procedure should have a header containing, a short & clear summary, description of parameters/default values/result set. Last in the header you should have links/tag(s) to your CMS/test tool/external documentation or a add the documentation directly. Complex code parts can have short local description. Simple code parts should never have documentation.
You were doing great up until that last sentence, which I totally disagree with. You should never have to read code to determine its basic intent. Of course, even though that has worked very well where ever I've worked, that's just my opinion, but I had to say it out loud.

Even though it might be obvious what simple code does, documentation goes a very long way to explain why it does it. I can't be the only person who's looked at some legacy code and seen something so seemingly daft that you can only assume it's deliberate. Knowing why something was done the way it was makes future work much easier. It may be you doing the future work and, knowing why the past (inexperienced, hungover, harassed) you did something the way they did, could save a lot of time.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

Neil Burton wrote:

Jeff Moden wrote:
jonas.gunnarsson 52434 wrote:
As in most development the code should speak for it self, and you should use:
Source control (containing change documentation)
Formatting tool/formatting rules
Good and describing naming rules
Your procedure should have a header containing, a short & clear summary, description of parameters/default values/result set. Last in the header you should have links/tag(s) to your CMS/test tool/external documentation or a add the documentation directly. Complex code parts can have short local description. Simple code parts should never have documentation.
You were doing great up until that last sentence, which I totally disagree with. You should never have to read code to determine its basic intent. Of course, even though that has worked very well where ever I've worked, that's just my opinion, but I had to say it out loud.
Even though it might be obvious what simple code does, documentation goes a very long way to explain why it does it. I can't be the only person who's looked at some legacy code and seen something so seemingly daft that you can only assume it's deliberate. Knowing why something was done the way it was makes future work much easier. It may be you doing the future work and, knowing why the past (inexperienced, hungover, harassed) you did something the way they did, could save a lot of time.

Heh... obviously, you're not the only person that's done such a thing which is why I absolutely agree that the "WHY" should be clearly stated for each statement and subquery. I tell people that you should be able to remove all code and the comments that remain should be good enough to draw a function flow chart of the process from.

Of course, I also tell people to draw such a chart and write each block as a comment in the new code before the write a lick of actual code is written but that seems to be a lost art for most anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Espo SSCrazy Points: 2678 More actions · Answer 11

Thanks everyone for the insight! I do have another question. I believe I know the answer but I need some more opinions

I created a User-Defined Table Type named xxxTable. This will be used as a parameter in a stored procedure.

The question is, when documenting this procedure, is the "Parameter Data Type" xxTable or User-Defined Table Type?

I say the "Parameter Data Type" xxTable and User Defined Table Type may be the "description".

Thanks in advance!

Michael

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 12

Both? I mean if we're going for clarity. Although, if I see xxTable as a parameters defined data type, I'm going to assume something custom.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL Espo SSCrazy Points: 2678 More actions · Answer 13

I believe I understand. The name "User-Defined Table Type" says that xxTable is a Data Type, but for the novice, that connection might not be made? Basically, it wont hurt to maybe include both as the "Parameter Data Type"?

Steve Collins SSCrazy Points: 2068 More actions · Answer 14

The type of the object (in C#) assigned to the SqlParameter is DataTable. The SqlDbType is SqlDbType.Structured.

SqlParameter lts = SqlCmd.Parameters.Add("@logged_times", SqlDbType.Structured);
lts.Direction = ParameterDirection.Input;
lts.Value = DataTableConverter.ToDataTable(model.Values);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können