How to Document Stored Procedures and Functions in Database

Table of Contents:


    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
    
    0
    There are no comments. Click here to write the first comment.