Same code in multiple stored procedures

Question

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.

Answer 1

This should work for you:

CREATE VIEW MyView AS
SELECT <colList>
FROM <TableOne>
INNER JOIN <TableTwo> ON ...
AND .....
AND .....
LEFT JOIN <TableThree> ON ...
AND .....
AND .....
WHERE .....
AND .....
AND .....

Then replace in Procs with:

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

• Jeff.Clark: Is there a way to make that single view propagate to all the databases that need it?
• Chad Mattox: Are the tables duplicated in the 20 databases? Or are they selecting from tables in a single database?
• Jeff.Clark: The tables (structure) are duplicated in those 20 databases. The Stored Procedures are similar (often using the same code chunks here and there) due to the nature of the business--Health Insurance EDI reporting for Obamacare. Each Database represents a different company, each Stored Procedure represents a different Insurance Carrier (Blue Cross/Kaiser/etc...)
• Chad Mattox: You would need to duplicate the view for each database then

Answer 2

This solution would replace the need for having 100 + procs doing the same thing. you have a proc and a function. The function splits all your medical codes from a string to a table which can be use in a CROSS APPLY in the new proc. This way you only have to call the one proc. Of course you'd have to update all the code calling the other procs to use just this one.

--gfn_ParseList
IF NOT EXISTS (SELECT * FROM sys.objects WHERE type in ('FN', 'IF', 'TF', 'FS', 'FT') AND name = 'gfn_ParseList')
 EXEC sp_executesql N'CREATE FUNCTION gfn_ParseList RETURNS @paresedIDs AS BEGIN SELECT 1 ParsedValue, 1 PositionID RETURN END'
GO
ALTER FUNCTION gfn_ParseList (@strToPars VARCHAR(8000), @parseChar CHAR(1))
RETURNS @parsedIDs TABLE
 (ParsedValue VARCHAR(255), PositionID INT IDENTITY)
AS
BEGIN
DECLARE 
 @startPos INT = 0
 , @strLen INT = 0
WHILE LEN(@strToPars) >= @startPos
 BEGIN
 IF (SELECT CHARINDEX(@parseChar,@strToPars,(@startPos+1))) > @startPos
 SELECT @strLen = CHARINDEX(@parseChar,@strToPars,(@startPos+1)) - @startPos
 ELSE
 BEGIN
 SET @strLen = LEN(@strToPars) - (@startPos -1)
 INSERT @parsedIDs
 SELECT RTRIM(LTRIM(SUBSTRING(@strToPars,@startPos, @strLen)))
 BREAK
 END
 SELECT @strLen = CHARINDEX(@parseChar,@strToPars,(@startPos+1)) - @startPos
 INSERT @parsedIDs
 SELECT RTRIM(LTRIM(SUBSTRING(@strToPars,@startPos, @strLen)))
 SET @startPos = @startPos+@strLen+1
 END
RETURN
END 
--New sp
create proc usp_ReturnSomeData (@medicalPlanCodes nvarchar(1000))
as
select YourColumn1, YourColumn2...
FROM <TableOne>
 CROSS APPLY gfn_ParseList(@medicalPlanCodes,',') p
 INNER JOIN <TableTwo> ON ...
 AND .....
 AND .....
 LEFT JOIN <TableThree> ON ...
 AND .....
 AND .....
WHERE .....
 AND .....
 AND .....