Reduce Code Duplication in Stored Procedure?

Question

Reduce Code Duplication in Stored Procedure?

charles.frank

SSC-Addicted

Points: 497
More actions
July 11, 2008 at 10:10 am

#93680

Is there a way I can reduce the duplication in this stored procedure? In each if statement, I have practically identical code except for one line in the lower statement. What I'm trying to do is return all the records if the user selects the "Default" report and a subset if the user selects a "Marketing" report. If the query where actually this small, it wouldn't be a big deal, but this is only an example. The real queries that will be in the if statements are large. I don't want to have to make changes to each block of code if I don't have to. Is there a way to reduce the code in here, or am I stuck having to do it this way?
Thanks,
Chuck
ALTER PROCEDURE [dbo].[spTEST]
(
@LineNum VARCHAR(10) = NULL,
@ReportType VARCHAR(20) = NULL
)
AS
IF @ReportType = 'Default'
BEGIN
SELECT ProdDesc, Size, UPC,
FROM Catalog
WHERE LineNumber = @LineNum
END
IF @ReportType = 'Marketing'
BEGIN
SELECT ProdDesc, Size, UPC,
FROM Catalog
WHERE LineNumber = @LineNum
-- This is the extra condition
AND Status <> 'DISCONTINUED'
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

there isn't really a good way to do this. You could move to dynamic SQL, but that's not really recommended. You could cause yourself other issues.

I can understand that you might not want to make 5 changes, but if they're all the same changes, then there are search and replace tools to make this work better and it can be cleaner. The other thing is to make separate stored procedures so you get nice clean execution plans, and call the various stored procedures from this one.

charles.frank SSC-Addicted Points: 497 More actions · Answer 2

Thanks for the advice. I've had the mindset of doing as much as I can in one stored procedure, but maybe in this case I should divide it.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

charles.frank (7/11/2008)
Is there a way I can reduce the duplication in this stored procedure? In each if statement, I have practically identical code except for one line in the lower statement. What I'm trying to do is return all the records if the user selects the "Default" report and a subset if the user selects a "Marketing" report. If the query where actually this small, it wouldn't be a big deal, but this is only an example. The real queries that will be in the if statements are large. I don't want to have to make changes to each block of code if I don't have to. Is there a way to reduce the code in here, or am I stuck having to do it this way?
Thanks,
Chuck
ALTER PROCEDURE [dbo].[spTEST]
(
@LineNum VARCHAR(10) = NULL,
@ReportType VARCHAR(20) = NULL
)
AS
IF @ReportType = 'Default'
BEGIN
SELECT ProdDesc, Size, UPC,
FROM Catalog
WHERE LineNumber = @LineNum
END
IF @ReportType = 'Marketing'
BEGIN
SELECT ProdDesc, Size, UPC,
FROM Catalog
WHERE LineNumber = @LineNum
-- This is the extra condition
AND Status <> 'DISCONTINUED'
END

Test out this query and see if it does what you are looking for:

SELECT

ProdDesc,

Size,

UPC

FROM

Catalog

WHERE

LineNumber = @LineNum

AND ((@ReportType = 'Default')

OR (@ReportType = 'Marketing' and Status <> 'DISCONINUED'))

😎

charles.frank SSC-Addicted Points: 497 More actions · Answer 4

Thanks, but @ReportType is a value being passed in from the user. It's not a field in the Catalog table. Each type of report queryies the same table, but the data returned is filtered by the Marketing report.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I only gave you the query, you'll need to put it into your proc.

ALTER PROCEDURE [dbo].[spTEST]

(

@LineNum VARCHAR(10) = NULL,

@ReportType VARCHAR(20) = NULL

)

AS

BEGIN