Today Aaron shares his implementation for applying the open/closed principle to SQL Server data and code. As stated previously it is more difficult to introduce a static set of parameters in SQL because TSQL does not support interfaces or inheritance.
The primary point of interfaces is that you can implement them in many different ways and still keep the contract consistent. Aaron emulates this practice using the following technique in SQL Server.
This can be done when the code has been designed using modularized code, For example, if a stored procedure returns a result set to a report and the solution was constructed in a manner that resembles something like:
- First a View(s) grabs the data
- Then a Table Value Function (tv-f) uses the View(s) to perform operations/filters that we don’t want in our View(s)
- Finally a stored procedure that calls the tv-f and includes any additional processing/ordering
If the solution is built as described above, it’s pretty straight-forward to implement the open-closed principal. A technique I’ve used many times over the last few years is that is to take all the logic from the stored procedure (if possible of course) and push it down a level into it’s own table-valued function.
You may now alter the stored procedure to simply call the new table valued function. So, I still have the same input signature on the original stored procedure, but all the data/logic is contained in a TVF. Then, if I need to alter the logic in some manner, I have options of either just changing the tv-functions, or if the new request can call a new stored procedure with a different input signature, by just creating a new stored procedure that calls the newly created TVF and includes any deviations from the original functionality. This way we have satisfied the new request without breaking the old procedure signature.
Just one way I’ve found that has worked well for me.
Aaron’s approach has two levels of managing change. The first is at the data level. The view or views hide the underlying data design allowing your code to continue to work should table modifications be needed. As long as the view always returns the same data, the underlying table structure may be modified radically without impacting consumers.
The second is the business logic you may introduce into your code. By using Table Value Functions you can enhance the functions in many different ways without having to change the parameters passed into a stored procedure consuming the function. You may even create a completely different function and call it from the stored procedure instead without changing the procedure name or parameters.
While this is much different than object oriented techniques, it definitely addresses the need to modify existing code, and maintain backward compatibility at the same time.
One thing that bugs me is to have a stored procedure with a perfect name, but I need a new definition of it to meet some newly identified requirement, resulting in a change to the input parameters or the returned results. Aaron’s approach doesn’t address this specific issue. One thing that bothers me is having stored procedures with a numeric suffix for each new version because the parameters changed and we need backward compatibility. Are there some best practices for this real world scenario? How do you solve the problem? Share here or by email to btaylor@sswug.org.
Cheers,
Ben