Same code in multiple stored procedures

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 
  INNER JOIN  ON ...
    AND .....
    AND .....
  LEFT JOIN  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.

This should work for you:

CREATE VIEW MyView AS
SELECT 
FROM 
INNER JOIN  ON ...
AND .....
AND .....
LEFT JOIN  ON ...
AND .....
AND .....
WHERE .....
AND .....
AND .....

Then replace in Procs with:

...
FROM MyView
WHERE
MedicalPlanCode IN ('abc', 'def', 'ghi')

StackExchange Database Administrators Q#139135, answer score: 11