Multiple stored procedures or 1 with parameter

Question

I need some advice...

I started working at a financial business world recently. Looks like the DB architect uses auto generate function to create stored procs. At the end, we have way too many procs doing almost 100% same thing.

Let's say, we have a table with a flag. He creates 3 different procs to pull out the data depends on this flag - all of them, 0 or 1.

When I change or fix something, I have to modify multiple procs because of this.

I think we just need to add a couple of parameters and just 1 proc. I can understand it's personal preference but as maintenance perspective, is this really good?

Answer 1

You are right that changing essentially identical code is a code smell.

The question is why does it smell?

The answer is rarely because someone copied and pasted code (although you should definitely consider it in the presence of novice developers, or code generators). It is usually a symptom of a larger architectural decision. Whether that decision needs revision is contextual.

So we need an example to go into some detail here:

Assume the business handles Students and Teachers with two endpoints in the API (/teacher and /student). Lets also assume the database has a single person table in the data base.

1. You can approach this by having a query that takes an argument and looks up people. Something like exec people_in_role @role='teacher'.

2. Or you can take the approach of two separate queries. exec students and exec teachers

In the first approach you have applied the DRY principle and removed the duplication in the query. But you have also introduced the idea of a Person into the business logic as the base type of both Teacher and Student.

In the second approach you have minimised the coupling between the domain concepts Teacher and Student keeping them independent and clean. On the other hand this has forced the database to expose two concepts as two stored procs, and duplicate what is essentially the same code.

Neither approach is strictly superior.

• Are you going to split the People table apart? Option 2 fits this situation better, as it doesn't impose any higher level coupling in the domain logic. Thus allowing the table to be split and change independently. But you will pay the price for maintaining near identical code.
• Do you already have a Person base class in the domain logic? Option 1 reduces code duplication. And aligns the Database with the domain logic for easier reasoning. But you will pay the price of having highly coupled domain logic.

In both cases what you are looking for is that sweet spot where the coupling isn't fighting how the system is being used, or being grown, but instead assists.