I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of that code in every SP that it occurred :)
It is a fairly hefty chunk of code, about 30 lines. The code is part of an insert statement, and it basically joins 4 tables together with
WHERE/AND conditions that do not really change from SP to SP. It looks similar to below:
... ... FROM <TableOne> INNER JOIN <TableTwo> ON ... AND ..... AND ..... LEFT JOIN <TableThree> ON ... AND ..... AND ..... WHERE ..... AND ..... AND ..... AND MedicalPlanCode IN ('abc', 'def', 'ghi')
The only part that changes from SP to SP are the values ('abc', 'def', 'ghi')
There also can be different amounts of those values, so some SP's will have 2 values, others will have 5, etc...
Everything I think of changes that section of code to dynamic SQL, and I am not sure if that is worth it. However, the programmer in me hates this situation.
Should I try to implement some form of code reuse? Would it have a ROI? What are my options? I had to go through ~100 stored procedures, which took about an hour.
The 100 SP's are spread out over 20 or so different databases. I do have permissions to create a view.