10 Reasons for Organizing Stored Procedure into Chapters and Steps

Paul Timar

March 29, 2015

minutes reading time

I made organizing Stored Procedures into Chapters and Steps a principle.

These separation points are marked by comments and variables used in performance and result logging.

A step is one specific technical or conceptual task that cannot be divided into further pieces without
leaving the abstraction of Stored Procedures or the task at hand. It could create an intermediate table, deletion of records, a calculation, or a logging step.

A chapter is a group of steps contributing to achieving one intermediate of the final result.
I assign steps a number and let them increase in increments greater than one.
The first digit of the step is the Chapter number to make it immediately apparent where the step stems from.

Why do I engage in what you might think is nothing but embellishment?
If only some of the following arguments seem convincing, you should seriously consider chapters and steps in the Stored Procedure code from now on:

1. Reflection of worthiness:

When you sketch what the SP shall do at the inception of your work, you might find out that what the SP shall perform is so little that it cannot be divided into two steps. You are then better off with ordinary SQL or the like and have just discovered by reflecting a little before writing one line of code.

2. Means of self-discipline

Steps and chapters are a good way to show where you stopped development the last time and where to continue.
This can be of great use in bustling workplaces where you cannot reserve long hours of cloistered coding.

3. Facilitation of documentation

Design and name your chapters and steps such that they can serve as titles for technical documentation.
Leave out the purely technical steps, and you have good points of departure for user manuals.

4. Unit of testing and debugging

I can only guess how much longer it would have taken me to find the bugs in the last SP I wrote if there hadn’t been these step numbers attached to where the SP failed!

5. Predefined elements of performance measurement

If you sense some of the tasks of an SP as potentially critical for system performance or are unsure how scalable the SP is, steps are ready-made units of performance measurement.

6. Standardization

Are you tired of getting accustomed to yet another SP outlook style with every new developer in the organization?
Make a difference by demonstrating to others how one element of standardization can look like.
Note that with mere tags in steps and chapters, individuality and freedom of thought are preserved!

7. Facilitation of communication

When someone is not satisfied with what you coded, both sides can save hours trying to see what the other does when they can walk over the code in terms of step numbers.

8. Evolutionary Advantage

Image that what you write is a bit like the DNA of an organism.
If that organism is made up of well-defined units that can be turned on or off, modified, or reused elsewhere, this is an advantage over fuzzy structures that lack any internal organization.

9. Data Flow control

With step numbers indicating where the data underwent the SP treatment passed through, you have already set the framework
for semantic tests after the SP has passed the earlier tests. This is especially valuable if the outcome looks correct but went
through the SP in unexpected twists and sidewalks.

10. Leaving a good legacy

I consider it comforting to know that even long after I have left a workplace for whatever reason, future generations will
be able to depart from where I finished much easier if they find code that is already organized and named. Organized code is a business card
you leave at the virtual desk.