Problem statement. Designing, writing and reviewing a database code is a large part of my daily routine. I have noticed that some simple rules around parameter selection can significantly improve the maintainability and scalability of some procedures. In this post, we will review a few guiding principles of how to design an interface for the procedures.
Real-life example. Imagine that you are driving a car. If you were deciding to turn to the left, you would turn the steering wheel counterclockwise and expect the car to turn the wheels in the left direction. It would be bizarre to also choose which set of wheels to turn, this information is redundant as there is no alternative. You also do not explicitly let the car know about the direction in which to turn – right or left, this information is implicitly derived from the direction of the steering wheel: clockwise is right, counterclockwise is left. You also do not enter the height of Jomolungma, as this information is irrelevant. Finally, you do not enter the speed of the car, to adjust the behavior of the car during the turn, even though this is relevant in the latest cars, this information already available in the car’s computer from the car sensors.
Car-human interaction design is very minimalistic and requires very limited input from the driver or passenger, many decisions are pre-defined or optimized based on the input parameters. Interface for a database procedure should also follow a few simple rules to avoid redundancy.
If we were designing parameters for the interface above in the procedure, it would look similar to
--correct CREATE PROCEDURE TURN ( @WheelRotationAngle FLOAT) --incorrect CREATE PROCEDURE TURN ( @WheelRotationAngle FLOAT, @WheelsToTurnList TOOLS.udt_Varchar, -- not needed, because we always need to turn both front wheels @TurnDirection Varchar(100), -- not needed, because it could be derived from the SIGN(@WheelRotationAngle) @JomlungmaHeight INT,-- not needed, because we do not use this variable in the procedure @Temperature FLOAT, -- not needed, as this information is available at any time );
We also do not need to pass context-specific information that is available in the session, such as current date, user and other. If data can be derived from a parameter then no need to pass it, for example, no need to send demographic information about a person, if we can derive it from his unique identifier by querying the corresponding table. This principle does not apply if derivation of the parameter requires significant resources and the final result is available in the calling point, then from the performance stand-point, it is better to pass redundant information.
If data for two or more parameters are available in the calling point, then no need to combine them into a single parameter, if later they need to be split again.
If more than ten parameter values are defined consider, combining them in the form of UDDT, JSON, XML or a bitmap.
In some cases a procedure is part of the larger group of procedures that supposed to follow particular naming convention and common parameter list, then, we may have parameters that are not used in the procedure, but has to exists in the parameter list.
Default behavior. Some cars, sports car, for example, or commercial airliners have much more complicated interfaces that require lots of tweaking, which, however, have some default settings.
We often specify default values for parameters to keep back compatibility of the previous version of the procedure, without changing all the calling points or if we want the ability to deviate from default behavior, for example specifying the large value for a page size in pagination pattern.
It is a good practice to keep all the parameters with default values at the end of the procedure, so in case if a procedure is called without explicitly providing the parameter names in the calling point, these default values still can be skipped.
Naming convention. One of my, and many other developers, the biggest challenges is providing the right names to the variable and parameters. Unless it is a generic procedure specify meaningful names for the procedures. @Param1 and @Param2 type of names are not consumer friendly. I am not a big fan of adding data type specific prefixes or suffixes to the variable name, however, one can decide for herself/himself what is the best. Overall follow your organizations naming convention, to reduce confusion.
Winston Churchill famous “the short words are the best, and the old words best of all” is quite applicable when choosing the name for your parameters.
SRP. Single responsibility principle is applicable to the procedure parameters as well. Avoid combining two or more different scopes to the same parameter. For example, in case if we need to pass a parameter about a state and behavior, I noticed that often, they are getting combined, especially if the number of states is small in the beginning. For a practical example, let’s say we are required to let procedure know if it has to override a value and what rule to use to override the value. One can choose a parameter that will pass the following values (‘Do not override’, ‘Update using Rule 1’, ‘Update using Rule 2’ etc.). The problem arises when we introduce a new state “Merge”. Ideally, we should have two parameters, one is responsible for the state “None”, Update”, “Merge” and one for behavior “Rule1”, “Rule2”
Changes to the procedure parameters. Changes are inevitable. To reduce the impact on existing calling points it is recommended to add all the new parameters to the end of the parameter list. If possible always add a default value to the new parameters and ensure that with default parameters the procedure behaves the same way as before. This way calls to the old version of procedures does not require any changes.
If changes are not back compatible then all the calling points will have to be altered. Consider creating a new procedure instead. In many cases, we can either nest the new procedure to the old one or vice versa to reduce the amount of codding.
In cases, if one or more parameters becomes obsolete, you will need to assess the impact of removing the parameter. If the changes required in the significant number of calling points, consider keeping the parameter, even though it is not used. This case is an exception to the previously described example with @JomlungmaHeight parameter above.
Summary. So summarizing all above here is the short list of best-practice
- Avoid redundancy, and keep the parameters list short
- Derive as much as possible
- Keep default values at the end
- Name parameters clearly
- Apply SRP to the parameters