Same code in multiple stored procedures
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')
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 .....