Re: Stored procedure/package with DateTime parameters need help

MrHelpMe 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.

CREATE OR REPLACE PROCEDURE demoproc (sdate DATE, edate DATE) IS   retval NUMBER;
BEGIN
   SELECT COUNT(*)
   INTO retval
   FROM all_objects
   WHERE created BETWEEN sdate AND edate;

   dbms_output.put_line(TO_CHAR(retval)); END demoproc;
/

set serveroutput on

exec demoproc(SYSDATE-100, SYSDATE)

HTH

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org