Re: Stored procedure/package with DateTime parameters need help

Mark D Powell wrote:
> On Jan 16, 9:40 am, "MrHelpMe" <clintto..._at_hotmail.com> wrote:
> > Hello experts,
> >
> > Is there some who could help me with this. I have a number of
> > packages/stored procedures created in oracle 9.2 that need to accept
> > parameters. The parameters I have having trouble with are a StartDate
> > and EndDate. If I have a package/SP with a start and end date how do I
> > pass these in my where clause and how do I allow the user to either
> > select NULL(so that the recordset returns all data regardless of Start
> > and End Date) or a Start and End Date is they are looking for a range
> > of values in the recordset. Please note that this SP will be hooiked
> > upto Crystal Reports and the start and end date will be passed to the
> > report in ASP.
> > i.e
> >
> > [CODE]
> > WHERE a.DateTime BETWEEN StartDate and EndDate...this is the part I am
> > unsure of
> > [CODE]
> >
> > Thanks everyone.
>
> What is the front-end tool written in?
>
> The SQL is fine as written though depending on where the data is coming
> from you are likely going to need to use to_date to convert the input
> parameters to date values. You can either check the input parameters
> for being NULL or have the front-end substitute sysdate for EndDate
> when the parameter is NULL. For StartDate you can just substitute a
> point in time well in the past.
>
> Assuming a usuable index on the date column exits then for the case
> when the entire date range is requested it would probably be better if
> the BETWEEN cause was not part of the SQL statement. This could mean
> that you want to code two separate SQL statements in the procedure and
> execute the one that fits the requested data. The reason being that if
> the index is chosen it will be beneficial for short range scans but
> will be inefficient for selecting all the data.
>
> A little performance testing may be called for using various
> percentages of the total date range to determine if any special
> handling is required.
>
> HTH -- Mark D Powell -

Mark,

Thanks for the reply. The Front end will be written in ASP. Oh I think I see what you are saying. So rather then use BETWEEN I could say
[code]
WHERE a.DateTime >= StartDate and a.DateTime <=EndDate [/code]

Is this what you mean. Also, could you see anything wrong with this beacuse I really don't

[code]
WHERE a.DATETIME BETWEEN NVL(StartDate,'1000-01-01') And NVL(EndDate,'9999-01-01')

or like you said
[code]
WHERE a.DATETIME >= NVL(StartDate,'1000-01-01') And

              a.DATETIME <= NVL(EndDate,'9999-01-01') [/code]

Thanks again Received on Tue Jan 16 2007 - 10:27:25 CST