Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored proc parameters Expand / Collapse
Author
Message
Posted Wednesday, March 04, 2009 8:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
I have 6 parameters to the stored proc, the following are the requirements:
1. when the proc is scheduled to run it should run for the previous day by not considering any of the parameters.
2. And also the proc can also be run on a adhoc basis by supplying just one parameter at a time.
So far I have the following code:

Create Procedure proc1
@p1,@p2, @p3, @p4, @p5, @p6
AS
Select * from table
where date1 >= DATEADD(Day, DATEDIFF(Day, 0, getdate()-1), 0)
and date1 < DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)
and
(
col1 = @p1 OR col2 = @p2
OR col3 = @p3
OR col4 = @p4
OR col5 = @p5
OR col6 = @p6 )

I am not sure, how to handle for the adhoc run when it's run for a single parameter at a time.
Please advice how I can do that in stored procedure.

Thanks!
Post #668865
Posted Wednesday, March 04, 2009 9:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 9:00 PM
Points: 291, Visits: 693
Use default values for your parameters and then check if any have been changed...

The following is untested but will give you an idea:
Create Procedure proc1
@p1 int =1,
@p2 int =1
AS
Select * from table
where date1 >= DATEADD(Day, DATEDIFF(Day, 0, getdate()-1), 0)
and date1 < DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)
and (col1 = @p1 or @p1=1 )
AND (col2 = @p2 or @p2=1)

In this code, if the parameter is 1 (the default) then every record will be true. If the parameter is changed then it will start to filter.



Bevan Keighley
Post #668868
Posted Thursday, March 05, 2009 4:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
Thanks, I will try and will let you know.
Post #669077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse