Benefits of Organizing Stored Procedures into Chapters and Steps

Paul Timar

April 28, 2023

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 refers to a technical or conceptual task that cannot be further divided without losing the abstraction of Stored Procedures or the main task. Examples of a step may include creating an intermediate table, deleting records, performing calculations, or logging information.

A chapter refers to a series of steps that collectively work towards accomplishing an intermediate stage of the final outcome. I designate each step with a number that increases in intervals greater than one, with the first digit of the number representing the chapter it belongs to, ensuring clear identification of its origin.

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 long it would have taken me to find the bugs in the last SP I wrote if these step numbers hadn’t been 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!

Facilitating Communication
In the event of dissatisfaction with the code you have written, valuable time can be saved by walking over the code together and referencing the 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.