Editorial Rss

Can You Implement the Open/Closed Principle in SQL?

Have you ever tried applying the Open/Closed principle to SQL Server stored procedures or functions? Since SQL Server is not an object oriented language it can be difficult to achieve, and may not be worth the effort. The point of the Open/Closed principle is to assure that existing code continues to work as you extend your code for newer behavior. 

Open/Closed states that the interface of an object/procedure parameters may not be modified so that existing clients do not break because they do not provide additional parameters added at a later time. However, the underlying code may be modified as necessary. 

In the case of a stored procedure or function you would not add another input parameter to an existing stored procedure. One way you could make it work is to have default values for additional parameters in a stored procedure. Another technique is to create a new stored procedure with the new parameters and perhaps have the old stored procedure call the new one with the default values specified.

Why would you go to the extra effort? The primary reason is so that your code is backwards compatible.

Is this worth your effort? Do you have your own methods of maintaining backward compatible code? Share your thoughts here or drop an email to