stored procedure help!! - 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 procedure help!!

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-16 : 15:59:33

I have created the following stored proc:
Create procedure proc1
@orderstatus VARCHAR(15),
@orderdate DATETIME,
@createdate DATETIME
AS
Select * from table1
where ordercreatedt >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()-1), 0) AND
ordercreatedt < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
and @orderstatus = case when @orderstatus = 'ALL' then 'ALL' Else orderstatus End
and isnull(@orderdate,'') = case when isnull(@orderdate,'') = '' then '' else orderdate end
and isnull(@createdate,'') = case when isnull(@createdate,'') = '' then '' else ordercreatedt end

The following is the requirement:
1. This proc will be scheduled to run as a SQL job every day to pick up the previous day orders, so the proc runs fine and gets the previous days data if I give the following parameters to the proc:
proc1 'ALL', null, null

2. The proc can also be run adhocly, by passing in just the @orderdate or @createdate for the below parameters:
proc1 'ALL', '03/01/2009', null --Should give the data for orderdate '03/01/2009'

proc1 'ALL', null, '02/05/2009' -- should give the data for createdate '02/05/2009'

I am having problems for the above 2nd requirement. Please let me know what changes should be done to the stored procedure so that both the requirements are met.
Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 16:56:14

[code]CREATE PROCEDURE dbo.uspMyProcedure
(
@orderstatus VARCHAR(15),
@orderdate DATETIME,
@createdate DATETIME
)
AS

SET NOCOUNT ON

SELECT @OrderDate = DATEDIFF(DAY, 0, @OrderDate),
@CreateDate = DATEDIFF(DAY, 0, COALESCE(@CreateDate, GETDATE() - 1))

SELECT *
FROM Table1
WHERE (@CreateDate IS NULL OR OrderCreateDT >= @CreateDate AND OrderCreateDT < DATEADD(DAY, 1, @CreateDate)
AND (@OrderDate IS NULL OR OrderDate >= @OrderDate AND OrderDate < DATEADD(DAY, 1, @OrderDate)
AND (@OrderStatus = 'ALL' OR @OrderStatus = OrderStatus)[/code]

E 12°55'05.63"
N 56°04'39.26"

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-16 : 18:04:17

I do'nt think it's working the way it should.

Actually we should use ordercreatedt field in pulling the previous day orders.

Select * from table1
where
ordercreatedt >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()-1), 0)
AND ordercreatedt < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
and @orderstatus = case when @orderstatus = 'ALL' then 'ALL' Else orderstatus End
and isnull(@orderdate,'') = case when isnull(@orderdate,'') = '' then '' else orderdate end
and isnull(@createdate,'') = case when isnull(@createdate,'') = '' then '' else ordercreatedt end

Actually in your code, I think you are using OrderCreateDT and OrderDate for pulling the previous day orders.
Please advice. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 18:17:32

See corrected version above.

E 12°55'05.63"
N 56°04'39.26"

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-03-16 : 18:27:06

It still does'nt work, thanks for helping me on this!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 18:42:10

[code]CREATE PROCEDURE dbo.uspMyProcedure
(
@OrderStatus VARCHAR(15),
@OrderDate DATETIME,
@CreateDate DATETIME
)
AS

SET NOCOUNT ON

SELECT @OrderDate = DATEDIFF(DAY, 0, @OrderDate),
@CreateDate = DATEDIFF(DAY, 0, @CreateDate)

IF @OrderDate IS NULL AND @CreateDate IS NULL
SET @CreateDate = DATEDIFF(DAY, 1, GETDATE())

SELECT *
FROM Table1
WHERE (@OrderDate IS NULL OR OrderDate >= @OrderDate AND OrderDate < DATEADD(DAY, 1, @OrderDate))
AND (@CreateDate IS NULL OR OrderCreateDT >= @CreateDate AND OrderCreateDT < DATEADD(DAY, 1, @CreateDate))
AND (@OrderStatus = 'ALL' OR @OrderStatus = OrderStatus)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources