stored proc parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored proc parameters

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-04 : 22:59:27

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!

matty
Posting Yak Master

161 Posts

Posted - 2009-03-05 : 00:27:21

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 = CASE WHEN @p1 IS NULL THEN col1 ELSE @p1 END
OR col2 = CASE WHEN @p2 IS NULL THEN col2 ELSE @p2 END
OR col3 = CASE WHEN @p3 IS NULL THEN col3 ELSE @p3 END
OR col4 = CASE WHEN @p4 IS NULL THEN col4 ELSE @p4 END
OR col5 = CASE WHEN @p5 IS NULL THEN col5 ELSE @p5 END
OR col6 = CASE WHEN @p6 IS NULL THEN col6 ELSE @p6 END
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources