I tried to put in one article all best practices for documenting stored procedures and functions in a relational database. Doing research for this article I found out that despite of those techniques have been here for many years and a lot of people asking questions there aren't many useful resources with compilation of best practices.

This article applies to SQL Server, Oracle or any other SQL relational database.

Header

Let's start with a header of procedure or function we are documenting.

Standard Header

There is not really something like a standard header, there are a few templates but they really boil down to basically the same. Let's have a look at SQL Server Management Studio standard header:

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

Out of this we could create something like this:

-- =============================================
-- Author:      Jack Brown
-- Create date: 2/3/2017
-- Description: Calculates discount for a particular customer order
-- =============================================

Input Parameters

Our standard header didn't include parameters so let's add Parameters section:

-- =============================================
-- ...
-- Parameters:
--   @customer_id - id of customer. Accepts nulls
--   @order_value - $ value of customer order
-- Returns:    Value of discount expressed as % (0-100)
-- =============================================

Return Value

If we are document a function we also need a documentation of value that is returned:

-- =============================================
-- ...
-- Returns:    Value of discount expressed as % (0-100)
-- =============================================

or like this:

-- =============================================
-- ...
-- Returns:    Value of discount expressed as % (0-100)
--   0 - no discount
--   0-100 - we give some discount
--   100 - we give stuff away for free
-- =============================================

Comment Code

SQL (and its derivatives T-SQL and PL/SQL) supports similar commenting techniques as most programming languages. You can embed comments in your procedures and functions code:

-- Simple short comment
/* Multi 
line 
comment */

Document Declarations

declare @discount int; -- discount value we are calculating
declare @magic_number double; -- no-one remembers where that comes from
declare @temp_val int; -- temporary variable

or like this

-- discount value we are calculating
declare @discount int; 

-- noone remembers where that comes from
declare @magic_number float = 2.5465; 

-- temporary variable
declare @temp_val int; 

Comment Blocks of Code

Just as with any other code, you should describe each block of code and explain what it does.

-- Magic happens here...
set @temp_val = 100 + datediff(day, '1996-09-07', getdate());
set @temp_val = @temp_val / log(@magic_number);
set @temp_val = @temp_val / pi(); 
set @temp_val = @temp_val * rand()*5;
set @discount = 5;

Code Separators

If you have many blocks of code in your function or procedure you might further divide it using separators as below:

------------- Declarations -----------------
...
---------- Calculating discount ------------
...

Change History

Database programming using SQL (T-SQL, PL/SQL) is left behind other programming languages. All the code is committed directly to code repository on the server. It is not possible to compile and run code locally. And there is no code version control built into popular databases. This means that, unless you copy code somewhere or use dedicated version control tool, you only have most recent version of each procedure or function and there is no way to check changes.

Best practice is to keep track of changes manually. Let's start with adding Change History section in our header and add a line each time we make a change.

-- =============================================
-- ...
-- Change History:
--   5/15/17 Jack Brown: Changed calcualtion method
--   5/16/17 Jack Brown: Changed calculation again
--   5/17/17 Mike White: Reverted all changes
-- =============================================

This needs to be maintained manually. You need to remember to add a description each time you commit a change.

Manual "Version Control"

If your procedure is long and you'd like to keep more detailed track of changes you could add comment each block of code and explain change in more detail. This comes at cost of readability and manual work though.

-- Magic happens here...
-- 5/15/17 Jack: changed pi() to exp(1) - Marketing request
-- 5/16/17 Jack: changed exp(1) to 1 - My own sense
-- 5/17/17 Mike: reverted all changes - CIOs order, Jack got fired
set @temp_val = 100 + datediff(day, '1996-09-07', getdate());
set @temp_val = @temp_val / log(@magic_number);
set @temp_val = @temp_val + pi(); 
set @temp_val = @temp_val * rand()*5;
set @discount = 5;

Who, When, What and Why

What you should put into that log is who and when made what change. It is also advised to put information why was this was changed and who requested that change. You could also add more details, like a ticked ID.

Putting It All Together

Here is what we get when we put it all together:

-- =============================================
-- Author:      Jack Brown
-- Create date: 2/3/2017
-- Description: Calculates discount for a particular customer order
--
-- Parameters:
--   @customer_id - id of customer. Accepts nulls
--   @order_value - $ value of customer order
-- Returns:     Value of discount expressed as % (0-100)
--
-- Change History:
--   5/15/17 Jack Brown: Changed calcualtion method
--   5/16/17 Jack Brown: Changed calculation again
--   5/17/17 Mike White: Reverted all changes
-- =============================================

CREATE PROCEDURE dbo.get_discount
    @customer_id int,
    @order_value float
AS
BEGIN

    ------------- Declarations -----------------

    -- discount value we are calculating
    declare @discount int; 

    -- noone remembers where that comes from
    declare @magic_number float = 2.5465; 

    -- temporary variable
    declare @temp_val int; 

    ---------- Calculating discount ------------

    -- Magic happens here...
    -- 5/15/17 Jack: changed pi() to exp(1) - Marketing request
    -- 5/16/17 Jack: changed exp(1) to 1 - My own sense
    -- 5/17/17 Mike: reverted all changes - CIOs order, Jack got fired
    set @temp_val = 100 + datediff(day, '1996-09-07', getdate());
    set @temp_val = @temp_val / log(@magic_number);
    set @temp_val = @temp_val + pi(); 
    set @temp_val = @temp_val * rand()*5;
    set @discount = 5;

    ------------ Returning value --------------

    return @discount;

END