8

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.

11

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')
  • Is there a way to make that single view propagate to all the databases that need it? – Jeff.Clark May 20 '16 at 19:57
  • 1
    Are the tables duplicated in the 20 databases? Or are they selecting from tables in a single database? – Chad Mattox May 20 '16 at 20:46
  • 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...) – Jeff.Clark May 23 '16 at 15:34
  • You would need to duplicate the view for each database then – Chad Mattox May 23 '16 at 17:38
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 .....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.