Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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.orgReceived on Tue Jan 16 2007 - 09:52:46 CST