Stored Procedure Documentation

  • SQL Espo

    SSCrazy

    Points: 2503

    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!

  • Grant Fritchey

    SSC Guru

    Points: 395846

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • John Mitchell-245523

    SSC Guru

    Points: 148519

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

    John

  • Grant Fritchey

    SSC Guru

    Points: 395846

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995466

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995466

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 395846

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1166

    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: 995466

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 22044

    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: 995466

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 2503

    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: 395846

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SQL Espo

    SSCrazy

    Points: 2503

    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"?

  • scdecade

    Mr or Mrs. 500

    Points: 561

    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

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

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