I made organizing Stored Procedures into Chapters and Steps a principle.
These points of separation are clearly marked by comments and by variables used in performance and result logging.
A step is one specifc technical or conceptual task that cannot be divided into further pieces without
leaving the level of abstraction of Stored Procedures or the task at hand. It could be the creation of an intermediate table, deletion of records, a calculation or a logging step.
A chapter is a group of steps that all contribute toward the achievement of one intermediate of 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, so that it is immediately clear 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 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 actually so little that it cannot be divided into two steps. Then, you are better off with ordinary SQL or the like and have just found out so by reflecting a little before even 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 yourself 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 as well.
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 not sure how scalable the SP is, steps are ready-made units of performance measurement.
Are you tired of getting accustomed to yet another style of SP outlook with every new developer in the organization?
Make a difference by demonstrating others how one element of standardisation can look like.
Note that with mere tags in the form of steps and chapters, individuality and freedom of thought is preserved!
7. Facilitation of communication
When someone is not satisfied with what you coded, both sides can save hours of 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 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 that 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 organised and named. Organised code is a business card
you leave at the virtual desk.
Paul Timar is a graduated Sociologist and Economist who turned Data Warehouse Specialist. He has more than 7 years of experience in working with Oracle and Teradata Data Warehouses in the fields of Social Security Data and Banking. His latest focus is on performance optimization.