SQL comment header examples

Question

Would just like too see what peoples Stored Procedure/Function etc comment headers look like (so post your examples)...I've only really seen what the SQL Server Management Studio creates but am interested in what other peoples look like...the formatting, characters used, procedure information/details etc I guess are what really makes them different...

SQL Server Management Studio (version 9) stored procedure comment header default:

-- =============================================
-- Author:      Name
-- Create date: 
-- Description: 
-- =============================================

Solution 1

I know this post is ancient, but well formatted code never goes out of style.

I use this template for all of my procedures. Some people don't like verbose code and comments, but as someone who frequently has to update stored procedures that haven't been touched since the mid 90s, I can tell you the value of writing well formatted and heavily commented code. Many were written to be as concise as possible, and it can sometimes take days to grasp the intent of a procedure. It's quite easy to see what a block of code is doing by simply reading it, but its far harder (and sometimes impossible) is understanding the intent of the code without proper commenting.

Explain it like you are walking a junior developer through it. Assume the person reading it knows little to nothing about functional area it's addressing and only has a limited understanding of SQL. Why? Many times people have to look at procedures to understand them even when they have no intention of or business modifying them.

/***************************************************************************************************
Procedure:          dbo.usp_DoSomeStuff
Create Date:        2018-01-25
Author:             Joe Expert
Description:        Verbose description of what the query does goes here. Be specific and don't be
                    afraid to say too much. More is better, than less, every single time. Think about
                    "what, when, where, how and why" when authoring a description.
Call by:            [schema.usp_ProcThatCallsThis]
                    [Application Name]
                    [Job]
                    [PLC/Interface]
Affected table(s):  [schema.TableModifiedByProc1]
                    [schema.TableModifiedByProc2]
Used By:            Functional Area this is use in, for example, Payroll, Accounting, Finance
Parameter(s):       @param1 - description and usage
                    @param2 - description and usage
Usage:              EXEC dbo.usp_DoSomeStuff
                        @param1 = 1,
                        @param2 = 3,
                        @param3 = 2
                    Additional notes or caveats about this object, like where is can and cannot be run, or
                    gotchas to watch for when using it.
****************************************************************************************************
SUMMARY OF CHANGES
Date(yyyy-mm-dd)    Author              Comments
------------------- ------------------- ------------------------------------------------------------
2012-04-27          John Usdaworkhur    Move Z <-> X was done in a single step. Warehouse does not
                                        allow this. Converted to two step process.
                                        Z <-> 7 <-> X
                                            1) move class Z to class 7
                                            2) move class 7 to class X

2018-03-22          Maan Widaplan       General formatting and added header information.
2018-03-22          Maan Widaplan       Added logic to automatically Move G <-> H after 12 months.
***************************************************************************************************/

In addition to this header, your code should be well commented and outlined from top to bottom. Add comment blocks to major functional sections like:

/***********************************
**  Process all new Inventory records
**  Verify quantities and mark as
**  available to ship.
************************************/

Add lots of inline comments explaining all criteria except the most basic, and ALWAYS format your code for readability. Long vertical pages of indented code are better than wide short ones and make it far easier to see where code blocks begin and end years later when someone else is supporting your code. Sometimes wide, non-indented code is more readable. If so, use that, but only when necessary.

UPDATE Pallets
SET class_code = 'X'
WHERE
    AND class_code != 'D'
    AND class_code = 'Z' 
    AND historical = 'N'
    AND quantity > 0
    AND GETDATE() > DATEADD(minute, 30, creation_date)
    AND pallet_id IN ( -- Only update pallets that we've created an Adjustment record for
        SELECT Adjust_ID
        FROM Adjustments
        WHERE
            AdjustmentStatus = 0
            AND RecID > @MaxAdjNumber

Edit

I've recently abandoned the banner style comment blocks because it's easy for the top and bottom comments to get separated as code is updated over time. You can end up with logically separate code within comment blocks that say they belong together which create more problems than it solves. I've begun instead surrounding multiple statement sections that belong together with BEGIN ... END blocks, and putting my flow comments next to the first line of each statement. This has the benefit of letting you collapse code block and be able to clearly read the high level flow comments, and when you branch one section open you'll be able to do the same with the individual statements within. This also lends itself very well to heavily nested levels of code. It's invaluable when your proc start to creep into the 200-400 line range and doesn't add any line bulk to an already long procedure.

Expanded

Collapsed

Solution 2

--
-- STORED PROCEDURE
--     Name of stored procedure.
--
-- DESCRIPTION
--     Business description of the stored procedure's functionality.
--
-- PARAMETERS
--     @InputParameter1
--         * Description of @InputParameter1 and how it is used.
--
-- RETURN VALUE
--         0 - No Error.
--     -1000 - Description of cause of non-zero return value.
--
-- PROGRAMMING NOTES
--     Gotchas and other notes for your fellow programmer.
--
-- CHANGE HISTORY
--     05 May 2009 - Who
--        * More comprehensive description of the change than that included with the
--          source code commit message.
--

Solution 3

-------------------------------------------------------------------------------
-- Author       name
-- Created      date
-- Purpose      description of the business/technical purpose
--              using multiple lines as needed
-- Copyright © yyyy, Company Name, All Rights Reserved
-------------------------------------------------------------------------------
-- Modification History
--
-- 01/01/0000  developer full name  
--      A comprehensive description of the changes. The description may use as 
--      many lines as needed.
-------------------------------------------------------------------------------

Solution 4

We use something like this and very useful for me .

/*  
Description:   
Author:   
Create Date: 
Param:   
Return:   
Modified Date:  
Modification:   
*/  

Solution 5

-- [why did we write this?]
-- [auto-generated change control info]

View more solutions

Comments

[Thor Hovden]

Exactly. Documenting why is so much more worthwhile than what. Although as a beginner, I would prefer why, what as well as how. :)

[Jeffrey Kemp]

"Date : yesterday" LOL

[unfinishedmonkey]

which integration is this? off-the-shelf or custom built?

[chazbot7]

@unfinishedmonkey custom - I built a process that scans objects for the pattern and stores the comments in a table.

[Nick Fotopoulos]

All depends on your environment. Believe it or not some places only means of version control of their database code is in the backups. Purity test are fun exercises for developers, but in the real world there are lots of things that prevent them from becoming a reality. "Procedure name" keeps the proc name at the top where you can see it. Sure the actual proc can name drift from the one in the header unnoticed, but people can also forget to check in their code. Nothing is perfect. shrug

[Nick Fotopoulos]

This header actually started as a path towards source control for a highly data driven organization.There were thousands of procs across hundreds of databases and dozens of SQL Server instances. First week I got the team setup with RegGate's SQL Source Control and had them checking in changes. That was great for changes going forward, but what about the history that came before git? There are procs that contain comments interspersed through out going back to1998 luckily with dates. Anytime a proc was updated all of these notes were aggregated at the top in chronological order.

[Nick Fotopoulos]

Dependency tools don't find dependencies buries in dynamic queries. While we all loath dynamic queries and want them to burn with the fire of a thousand suns, there are some that we can't eliminate....not yet anyway. They would need dozens of developers to update everything in a reasonable time frame and like most places they were struggling to even locate enough devs to keep up with critical projects. Few procs actually have ALL of these fields in them, they usually only have the ones that are actually useful. For many things the list is much shorter.

[Nick Fotopoulos]

Last year just before COVID hit I reached out to the person who created a proc in 2009 who is still with the organization and they were able to help me understand the intent of the proc I was working on. I wasn't expecting them to remember but I was desperate and I could tell exactly what the code was doing but why it was doing it was a mystery the code itself was never going to reveal. Sometimes knowing the author helps you understand the code because you've seen enough stuff written by them. Hope this helps!

[Rab]

Oh indeed; you're absolutely right - there IS of course a reason for doing precisely what your describing. I've worked there, in messy devops scenarios where fileshares store scores of disorganised scripts. My point is that someone in the organisation needs to take a step back and take responsibility for this. If the environment is "modern", a comment need be nothing more than a copyright notice and a brief synopsis of the script for anyone calling it. Alarm bells should be going off in your head if this is required to be maintained in the script.

[Rab]

And indeed, regarding your further comments, it can be incredibly useful having the author too; however the point is that this is also contained within SVC.