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 procedure parameters question Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 1:27 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 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 convert(varchar(10),ordercreatedt,101) 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!
Post #676869
Posted Monday, March 16, 2009 2:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 8:09 AM
Points: 51, Visits: 118
Try this

CREATE procedure proc1
@orderstatus VARCHAR(15),
@orderdate DATETIME,
@createdate DATETIME
AS
IF @OrderDate is NULL and @Createdate is NULL
Begin
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

END
ELSE
BEGIN
Select * from table1
where @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 convert(varchar(10),ordercreatedt,101) end

END
Post #676918
Posted Monday, March 16, 2009 2:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Hello

What I understood:
* @orderstatus may be ALL or an specific status
* @orderdate may be NULL or a specific date
* @createdate may be NULL or a specific date, but the time has to be cutted off.

So try this:
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 orderstatus ELSE @orderstatus END
      
AND orderdate = ISNULL(@orderdate, orderdate)
      AND
ordercreatedt = ISNULL(CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @createdate))), ordercreatedt)


Correct me if I'm wrong.

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #676932
Posted Wednesday, March 31, 2010 6:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 4:16 AM
Points: 303, Visits: 165
Try This :


Create procedure proc1
@orderstatus VARCHAR(15),
@orderdate DATETIME,
@createdate DATETIME
AS
Select * from table1
where ordercreatedt >= CAST(CONVERT(Varchar(10),GetDate()-1,120) AS DateTime)
AND ordercreatedt < CAST(CONVERT(Varchar(10),GetDate(),120) AS DateTime)
and orderstatus = case when @orderstatus = 'ALL' then orderstatus Else @orderstatus End
and orderdate = ISNULL(@orderdate, orderdate)
and ( @createdate Is Null Or convert(varchar(10),ordercreatedt,101) = convert(varchar(10),@createdate,101) )
Go


Regards,
ѕι๋∂∂нєѕн
Post #893718
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse