|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261,
Visits: 1,492
|
|
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!
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 282,
Visits: 670
|
|
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
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261,
Visits: 1,492
|
|
| Thanks, I will try and will let you know.
|
|
|
|