Re: Stored procedure/package with DateTime parameters need help

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 -- Received on Tue Jan 16 2007 - 09:33:26 CST