What is better- Add an optional parameter to an existing SP or add a new SP?
database, database-design, sql-server, stored-procedures
Solution
Quoting from a disadvantage for your first option:
It will be difficult to handle if same report gets modified again with more requests – will mean more flags and code will become un-readable.
Personally I feel this is the biggest reason not to modify an existing stored procedure to accommodate the new columns.
When bugs come up with a stored procedure that has several branches, it can become very difficult to debug. Also as you hinted at, the execution plan can change with branching/if statements. (sql using different execution plans when running a query and when running that query inside a stored procedure?)
This is very similar to object oriented coding and your instinct is correct that it's best to extend existing objects instead of modify them.
I would go for approach #2. You will have more objects, but at least when an issue comes up, you will be able to know the affected stored procedure has limited scope/impact.
Over time I've learned to grow objects/data structures horizontally, not vertically. In other words, just make something new, don't keep making existing things bigger and bigger and bigger.
Problem
I have a production SQL-Server DB (reporting) that has many Stored Procedures. The SPs are publicly exposed to the external world in different ways - some users have access directly to the SP, - some are exposed via a WebService - while others are encapsulated as interfaces thru a DCOM layer. The user base is large and we do not know exactly which user-set uses which method of accessing the DB. We get frequent (about 1 every other month) requests from user-sets for modifying an existing SP by adding one column to the output or a group of columns to the existing output, all else remaining same. We initially started doing this by modifying the existing SP and adding the newly requested columns to the end of the output. But this broke the custom tools built by some other user bases as their tool had the number of columns hardcoded, so adding a column meant they had to modify their tool as well. Also for some columns complex logic is required to get that column into the report which meant the SP performance degraded, affecting all users - even those who did not need the new column. We are thinking of various ways to fix this: 1 Default Parameters to control flow Update the existing SP and control the new functionality by adding a flag as a default parameter to control the code path. By using default parameters, if value of the Parameter is set to true then only call the new functionality. By default it is set to False. Advantage - New Object is not required. - On going maintenance is not affected. - Testing overhead remains under control. Disadvantage - Since an existing SP is modified, it will need testing of existing functionality as well as new functionality. - Since we have no inkling on how the client tools are calling the SPs we can never be sure that we have not broken anything. - It will be difficult to handle if same report gets modified again with more requests – will mean more flags and code will become un-readable. 2 New Stored procedure A new stored procedure will be created for any requirement which changes the signature(Input/Output) of the SP. The new SP will call the original stored procedure for existing stuff and add the logic for new requirement on top of it. Advantage - Here benefit will be that there will be No impact on the existing procedure hence No Testing required for old logic. Disadvantage - Need to create new objects in database whenever changes are requested. This will be overhead in database maintenance. Will the execution plan change based on adding a new parameter? If yes then this could adversely affect users who did not request the new column. Considering a SP is a public interface to the DB and interfaces should be immutable should we go for option 2? What is the best practice or does it depend on a case by case basis, and what should be the main driving factors when choosing a option? Thanks in advance!