March 4, 2009 at 8:52 pm
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!
March 4, 2009 at 9:09 pm
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
March 5, 2009 at 4:35 am
Thanks, I will try and will let you know.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply