How to Make SQL Server Stored Procedures More Readable

best practicesmaintenanceperformancestored-procedures

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So… is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

UPDATE – Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

  • Length (hundreds of lines of code each)
  • Dynamic SQL
  • Control flow
  • Nested queries (sometimes two or even three levels)
  • Combinations of all of the above 🙁

UPDATE 2:

I'm using Sql Server 2k5/2k8

Best Answer

As you said, start with easy to read consistent formatting. Here is a general idea of how I like to do it, although it's by no reason the only good way to format.

DECLARE @Test int

IF @Test = 1
BEGIN
    -- Indent within blocks
    PRINT @Test
    SELECT Field1, Field2, -- If the list get's long 
            Field3, Field4 -- split into multiple lines
    FROM Table1
    -- Line up JOIN statement and FROM statement
    JOIN Table2
        -- Indent ON condition with one condition per line
        ON Table1.Field1 = Table2.Field2
        AND Table1.Field2 = Table2.Field2
    WHERE Field1 = @Test
      AND Field2 = 'ABC'
END

On top of that you will notice that I've got comments all over the place. Including in the middle of the SELECT statement and at the end of some lines. Over commenting can make it more difficult to read also so you need to find a happy balance. Another commenting trick is to include "block comments" like this:

/************************************************
********* Title for section 1 *******************
************************************************/

These help you divide up your code into obvious sections. Say "Variable Declarations and Initialization", "Loading Temporary Tables", "Generating Dynamic SQL", etc.

For subqueries I tend to find CTEs and even nested CTEs easier to read although in some cases a mix is best.

SELECT *
FROM (
    SELECT *
    FROM (
            SELECT *
            FROM Table1
        ) Level2
    ) Level1

vs

WITH 
    Level2 AS (
        SELECT *
        FROM Table1 ),
    Level1 AS (
        SELECT *
        FROM Level2)
SELECT *
FROM Level1

And a little SSMS trick while you are viewing your SPs. Pay attention to those little - signs next to the code.

enter image description here

Click on one of them and it will collapse the block below it. Doing this you can get a similar effect to separating things out as stored procedure calls.

enter image description here

Of course your mileage will vary with any of these and in some cases a monster SP is just a monster.

Related Question