Benefits of Organizing Stored Procedures into Chapters and Steps

sql3

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.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

A step refers to a technical or conceptual task

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:

Get the next issue by email.

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.

1. Reflection of

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!

4. Unit of testing and debuggingI can only guess

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. 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.

Facilitating CommunicationIn the event of

8. Evolutionary Advantage

Imagine 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 detours.

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.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.

A step refers to a technical or conceptual task

1. Reflection of

4. Unit of testing and debuggingI can only guess

Facilitating CommunicationIn the event of

Subscribe to DWHPro Letters