Which is better - add an optional parameter to an existing SP or add a new SP?

Question

I have a SQL-Server production database (report) that contains many stored procedures. SPs are publicly exposed to the outside world in many ways
- some users have access directly to the SP,
- some of them are submitted through WebService
- while others are encapsulated as interfaces through the DCOM layer.

The user base is large, and we do not know exactly which user set uses that method of access to the database.
We receive frequent (about 1 time per month) requests from user sets to modify an existing SP, adding one column to the output or a group of columns to the existing output, and the rest remain the same.
We started by doing this by modifying the existing SP and adding the newly requested columns to the end of the output. But this violated the user tools created by some other user bases, as their tool had a number of hard-coded columns, so adding a column meant that they also needed to change their tool. Also, some columns require complex logic to get this column in the report, which meant SP performance degradation affecting all users - even those who do not need a new column.

We are thinking of various ways to fix this:

1 Default options for flow control

Upgrade your existing SP and configure new features by adding a flag as the default parameter for controlling the code loop. Using the default parameters, if Parameter is set to true, then only call new functions. The default value is False.

Advantage

• A new facility is not required.
  
• It does not affect ongoing maintenance.
  
• The overhead of testing remains under control.
  

Inconvenience

• Since the existing SP is modified, it will require testing of existing functionality, as well as new functionality.
  
• Since we have no idea what client tools call SP, we can never be sure that we haven't broken anything.
  
• It will be difficult to process if the same report is again modified with a large number of requests - it will mean that more flags and code will become unreadable.

2 New stored procedure

A new stored procedure will be created for any requirement that changes the signature (input / output) of the SP.
The new SP will call the original stored procedure for existing things and add new logic to it.

Advantage

• The benefit here will be that there will be no impact on the existing procedure, so there is no need to test for the old logic.
  

Disadvantage

• You must create new objects in the database each time you request changes. This will be the overhead of maintaining the database.
  
  

Will the execution plan be changed based on the addition of a new parameter? If so, this may adversely affect users who have not requested a new column.
Given that SP is an open interface to the database, and the interfaces should be unchanged, if we go to option 2?
What is the best practice or does it depend on the individual basis and what should be the main driving factors when choosing an option?

Thanks in advance!

Answer 1

Quote from the flaw for your first option:

It will be difficult to process if the same report is changed again with a large number of requests - it will mean that more flags and code will become unreadable.

Personally, I think this is the biggest reason not to modify the existing stored procedure to accommodate new columns.

When errors appear with a stored procedure that has several branches, it can be very difficult to debug. Also, as you hinted, the execution plan can be changed using the / if branch expressions. ( sql using different execution plans when starting a query and when executing this query inside a stored procedure? )

This is very similar to object-oriented coding, and your instinct is correct that it is best to extend existing objects, rather than modify them.

I would go for approach number 2. You will have more objects, but at least when a problem arises, you will be able to find out that the affected stored procedure has a limited scope / impact.

Over time, I learned to grow objects / data structures horizontally, not vertically. In other words, just do something new, don’t keep doing existing things more and more and more.

Answer 2

Ok # 2. Definitely. Without a doubt.

# 1 says, “modify the existing procedure,” causing things to break. This is by no means a good thing! Your customers will hate you. Your code just gets more complicated, so it's harder and harder to avoid breaking things that lead to more hatred. This will go terribly slow and will be impossible to tune. And so on.

For # 2 you have a stable interface. No hate. Hooray! Seriously, "yay," as in "I still have a job!" unlike "boo, I got fired for being annoyed by my customers." Jokes aside. Never do # 1 just for this reason. You know that is true. You know it!

Having said that, write down what people do. Take user id as parameter. Log in. Know your users. Find those who use the old shitty code and ask them to pleasantly update if necessary.

Your reason to avoid the number 2 is distribution. But this is only a problem if you are not testing the material. If you test the material correctly, proliferation still occurs in your tests. And you can always tweak things in # 2 if necessary, or at least isolate performance issues.

If the thicker procedure is really great, then modify the skinny version with the thinner version of the thick one. In SQL, this is difficult, but copy / paste and cut the list of favorite columns. In general, I just did not bother to do this. Life is too short. Having really good test code is a much better investment of time, and the data schema is usually rarely changed in ways that violate existing queries.

Good. Hurry up. Serious message. Do # 2, or at least DO NOT # 1, or you will be fired, or hated, or both. I can’t think of a better reason than this.

Answer 3

# 2 may be a better option than # 1, especially given the bullet 3 flaws of # 1, as the requirements change in most cases. I feel this because disadvantages prevail here than advantages on both sides.

Answer 4

It’s easier to go with No. 2. Nullable SP parameters can create some very difficult to find errors. Although, I use them from time to time.

Especially when you start joining the zeros and ANSI settings. The way you write a query will dramatically change the results. KISS. (Keep things just stupid).

Also, if it is a parameterized search for presentation or display, I could consider ultra-fast fetching of data into a LINQ object. Then you can search the list in memory, rather than retrieve it from the database.

Answer 5

I would also vote for # 2. I saw several stored procedures that take number 1 to the extreme: SP has the @Option parameter and several parameters @param1 , @param2 , .... The network effect is that it is one saved A procedure that attempts to play the role of many stored procedures.

The main disadvantage of No. 2 is the availability of more stored procedures. You may find it difficult to find the one you are looking for, but I think it’s a small price to pay for the other benefits that you get.

I also want to make sure that you are not just copying and pasting the original stored procedure and adding some columns. I also saw too many of these. If you add only a few columns, you can call the original stored procedure and join the new columns. This will inevitably result in a performance penalty if these columns were easily accessible before that, but you don’t have to change the original stored procedure (refactoring to ensure good performance and no code duplication), and you do not need to maintain two copies (copy and paste for execution).

Answer 6

I am going to offer several other options based on the options you gave.

Alternative # 1: add another variable, but instead of turning it into a default variable, the variable is not specified in the client name. Thus, client A can receive his own specialized report, and client B can receive his slightly different individual report. This adds a ton of work, since updates in the "Home" section should be copied to all special customers. You can do this with "if" expressions.

Alternative # 2: Add new stored procedures, just add the client name to the stored procedure. Accounting is wise, it may be a little more complicated, but it will achieve the same end results, each client receives his own type of report.

Answer 7

Option number 2 is selected.

You yourself mentioned (no) benefits.

While you are considering adding new objects to db based on changing requirements, add only the necessary objects that do not make your new SP bigger and more difficult to maintain.