Novick Software
SQL Server Consulting • Design • Programming • Tuning

  andy novick is a sql server mvp

 

 

Making Stored Procedures Readable

Volume 3 #12    August 31, 2005  Full TOC

by Andrew Novick

Sign up for this newsletter at: http://www.novicksoftware.com/coding-in-sql/coding-in-sql-signup.htm

Stored procedures can grow to be quite lengthy.  I've created procedures that are 500 to 1000 lines or more.  There are a couple of reasons for this, including the limited coding structure provided by T-SQL.  In particular, the limited error handling in SQL Server 2000 forces constant testing of error values.  This sometimes leads to somewhat awkward procedures.  Over the years I've developed some practices for writing large stored procedures that I'd like to share with you.

As far as I'm concerned most complex programs shouldn't be written in T-SQL.  They should be in a client program or a batch program that can be run on a server other than the one hosting SQL Server.  The reasons for this are mostly economic, due to the cost of running code inside SQL Server and in particular, the cost of scaling up the SQL Server. However, there are other economies at work.  In particular, the cost of programmer productivity, training, and operations.  These factors may weigh in favor of T-SQL stored procedures.

Yet another set of factors is performance.  A T-SQL stored procedure runs in the SQL Server database engine and its access to data is as close as possible.  The result can be staggeringly higher performance, in terms of run-time, than a program running in another tier of the application.

So I accept that there are times when large T-SQL stored procedures are necessary.  Given that, I'd like to make them as maintainable as possible.  This is done with several strategies:

  • Proper naming of the procedure.
  • Documentation in the program header
  • Breaking stored procedures down into subprocedures.
  • Formatting the T-SQL for readability.

Previous articles have discussed the naming of database objects including stored procedures.  You might want to go back and look at Volume 3 #10 and #11, which are about naming.  In addition, as I started the "Style" theme for Coding-in-SQL, Volume 3 #9 is about Seperator First Formatting (SFF) for all T-SQL.  I'll assume that you're familiar with these issues and concentrate on the last two items.  Breaking stored procedures into subprocedures and the layout of large stored procedures.

Back when I was a freshman at Brown and took Computer Science 100, Professor Van Damm taught us about creating subroutines.  Programs should be readable and almost all subroutines should be less than a page in length.  Of course, the only way to see your subroutine was on greenbar paper or flipping through your card deck.  So the 66 line subroutine limit sort of got engrained in my thought process.

Things have changed and we hardly see programs on paper anymore.  The screen is the most common vehicle for reading most programs and the number of lines that you can see at a time is much more limited than on a full sheet of paper.  I can get around 40 lines on my screen when using SQL Query Analyzer.  Of course, I'm kind of old and I've pumped up the font size so I can read it.  I'm sure some of you can see more lines. 

While I generally like to break code into manageable chunks, there's a down side to this practice: poor performance.  The SQL Server database engine isn't a compiler it's an interpreter.  Calling a stored procedure from another stored procedure requires that SQL Server at least consider whether it must create a new plan for the called stored procedure.  Since the driving reason for creating a stored procedure may be the performance, using a technique that slows you down does the opposite of the original intent.  Overall, I've had to strike a balance between making sort subroutine type stored procedures and longer procedures that perform better but might be more difficult to read.

One of the things that I've learned about breaking any code down is that it isn't really necessary if the code is linear.  That is if there are no loops and the code only flows from top to bottom, I can live without breaking it down into subroutines.  This helps a great deal when writing T-SQL because a routine can often be arranged this way. 

Error handling is another major factor in the layout of a stored procedure.  As I've written before T-SQL lacks any sort of structured exception handling.  You're really on your own as far as structure goes.  My typical structure for error handling is to test for errors after every significant statement and if an error is found, set the error message and then GOTO an error handing block at the end of the procedure.  Yes, you got that right, I use GOTO all the time to handle errors.  Professor Van Damm would be rolling over in his grave, if he were dead. 

What I’ve found over time is that trying to do error handling in T-SQL without resorting to the GOTO statement makes for much more convoluted code.  Lot’s of indented IF blocks and the like.  In most procedures, I can keep the nesting level to a minimum and keep the code pretty much linear from beginning to end.  The same holds for many Visual Basic 6 programs.  VB6 also lacks structured exception handling and the “ON ERROR GOTO” statement is a form goto that you can’t live without.  Fortunately, Visual Basic.Net and C# have very robust structured exception handling.

Each section of my typical long stored procedure is layed out approximately like this:

-----------------------------------------------------------------
-----------------------------------------------------------------
-- Comments about the section go here.

-- Possibly an additional comment about the next statement

DELETE or SELECT or INSERT …

   
SELECT @myError = @@Error, @myRowCount = @@Rowcount
   
IF @myError = 0 BEGIN
    SET @NumDelMsr = @myRowCount
   
SET @Msg = 'Message describing success …’
   
exec usp_AppSession_Trace @TraceMsgID output, @TraceSesID
             ,@myTLevel , 2,@Msg,@TraceSource,@TraceCategory
   
END
ELSE BEGIN
    SET @Msg = 'message describing the error …’

    exec usp_AppSession_Trace @TraceMsgID output, @TraceSesID
             ,@myTLevel, 1,@Msg,@TraceSource,@TraceCategor
   
RAISERROR (@Msg, 16, 1) – So the message is in the output.
   
SET @WorstError = CASE WHEN @WorstError < @myError
                          
THEN @MyError ELSE @WorstError END
    GOTO GETOUT
END
 

The double line of dashes set the section apart from the other sections.  That’s followed by the SQL Statement that really does the work.  Next is the statement:

   
SELECT @myError = @@Error, @myRowCount = @@Rowcount
   
This statement is needed after every SQL statement that could set an error code of interest.  Typically these are SELECT, INSERT, UPDATE, DELETE, and EXEC statements.  It’s important to retrieve the value of @@ERROR immediately after it might have been set because the next T-SQL Statement is going to reset @@ERROR.

Once the value of @@ERROR and @@ROWCOUNT are safe in variables, they can be used in determining if the statement succeeded and what to do about it.  Usually for an error, I try and write a trace statement, leave the message in the results, and GOTO the GETOUT label, which is at the end of the procedure.  If the error forces a rollback, that may also be necessary.  In that case, trace messages written to the AppSessionTrace table are going to be wiped out by the rollback, but the message in the procedures output in the RAISERROR statement will get back to the caller.

The @WorstError variable keeps the worst error code so that it can be used as the return code for the proc.  Of course, if the first error is all that you’re going to allow, you can dispense with @WorstError.

At the bottom of the procedure is the GETOUT: label that is referenced in the GOTO.  Here’s the layout of the typical bottom of a long procedure:

-----------------------------------------------------------------
-----------------------------------------------------------------
-- Wrap Up the Job

GETOUT:

SET @Msg = ‘Construct a final message here.’

EXEC usp_AppSession_End  @TraceSesID
         , 'AppSession|AppSessionTrace|AppSessionMeasurement'
         , @Msg
         , '|'

RETURN @WorstError

For a full blown example, I refer you to a recent article, Volume 3 #7, Trace Tables Part V: Purging Trace Tables.  The procedure usp_AppSessionTrace in that issue follows my typical procedure layout.

The next topic is SQL Server 2005 programming with the .Net CLR.  This should be interesting.  I’m making two presentations about .net CLR Programming at Microsoft’s Code Camp V on September 24-25.   If you’re in the Boston Area, you should regester and come check it out.  I’d love to meet you.

Transact-SQL User-Defined Functions has been published!  Take a look at it now!


Do you have an interesting stored procedure, user-defined-function, or other sample code?  If you'd like to share them please send them in an email to: coding-in-sql@NovickSoftware.com  and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andy
Andrew Novick


RSS as HTML

Personal Blog

 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule