December 1, 2005 at 3:05 pm
I have an existing sp, usp_Proc1. I have another sp, usp_Proc2, that is
exactly like usp_Proc1 except for one part of the where condition is different.
How can I combine the two procs into one so that I can pass in a flag that says if
flag=1, use the where clause from proc1, else use the where clause from proc2?
I know I could do an If statement at the top and repeat both sets of code, but that
seems redundant and the procs are actually a bit more complicated than this example.
thanks!
ALTER PROCEDURE [dbo].[usp_Proc1]
@col1 int,
@col2 int
AS
Select count(col6)
from TableA
Where col1 = @col1 and
col2 = @col2 and
col3 = 'U' and --***
col4 Not In (1,2,3)
GO
ALTER PROCEDURE [dbo].[usp_Proc2]
@col1 int,
@col2 int
AS
Select count(col6)
from TableA
Where col1 = @col1 and
col2 = @col2 and
col8 = 1 and --***
col4 Not In (1,2,3)
GO
December 1, 2005 at 3:36 pm
ALTER PROCEDURE [dbo].[usp_Proc1]
@col1 int,
@col2 int,
@col03 char(1),
@col08 int
AS
Select count(col6)
from TableA
Where col1 = @col1 and
col2 = @col2 and
(col3 = @Col03 or @col03 IS NULL) and
(col08 = @Col08 or @col08 IS NULL) and
col4 Not In (1,2,3)
GO
_____________
Code for TallyGenerator
December 1, 2005 at 3:58 pm
I want to either use col3 or col8, but not both.
December 1, 2005 at 4:11 pm
Assign NULL to corresponding parameter and column will not be used.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply