stored procedure parameters question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » stored procedure parameters question

stored procedure parameters question

Rate Topic

Display Mode

Topic Options

Author

Message

Mh-397891

Posted Monday, March 16, 2009 1:27 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 2:28 PM
Points: 261, Visits: 1,492

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

Sanjay-300840

Posted Monday, March 16, 2009 2:25 PM

Valued 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

Florian Reischl

Posted Monday, March 16, 2009 2:37 PM

SSCrazy

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

Siddhesh

Posted Wednesday, March 31, 2010 6:53 AM

Old 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 »

Permissions