July 11, 2008 at 10:10 am
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
July 11, 2008 at 10:14 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 11, 2008 at 10:21 am
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
July 11, 2008 at 10:36 am
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'))
😎
July 11, 2008 at 10:49 am
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.
July 11, 2008 at 11:14 am
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
SELECT
ProdDesc,
Size,
UPC
FROM
Catalog
WHERE
LineNumber = @LineNum
AND ((@ReportType = 'Default')
OR (@ReportType = 'Marketing' and Status <> 'DISCONTINUED'))
END
😎
July 11, 2008 at 12:32 pm
Works! I didn't know how to get the parameters to work in the "where" if they weren't fields. Thanks.
🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply