Stored Procedure: Date IN Parameter

Hello,

I am writing a stored procedure that has to get records between certain dates. These dates will be entered as parameter with the sp.

execute sp_getRecords ('2006-01-01', 2006-06-01')

Here is a snippet:

Create or Replace procedure sp_getRecords (startDate IN Varchar2, endDate IN Varchar2)
AS
Begin

Declare
dtStartDate long;
dtEndDate long;
strInsert long;
Begin
dtStartDate := to_date(startDate,'YYYY-MM-DD');
dtEndDate := to_date(endDate,'YYYY-MM-DD');

strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= dtStartDate AND
recordDate < dtEndDate
)';


execute immediate strInsert;

end
end;

---

When i run this, i get an error saying "invalid identifier" at dtEndDate in Insert record command.

Edited at 10am on Sep 19, 2007:

dtEndDate := to_date(endDate,'YYYY-MM-DD');

ERROR at line 1:
ORA-00904: "DTENDDATE": invalid identifier
ORA-06512: at "adm.SP_GETRECORDS", line 30
ORA-06512: at line 1

If i delete 'dtenddate' and only leave 'dtstartdate' in. I get the same error on dtStartDate.

Any help is much appreciated.

Thanks,

J

dtStartDate and dtEndDate have incorrect data type, the must be DATE type.

dtStartDate DATE;
dtEndDate DATE;

Thanks for replying.

I am sorry. That was a mistake when posting here. I have:
dtStartDate Date;
dtEndDate Date;
in the code.

strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= ' || dtStartDate || ' AND
recordDate < ' || dtEndDate
)';

Thanks for replying shamshe.

I get "JUN" as invalid identifier now when i put:

execute sp_getRecords ('2006-01-01', '2006-06-01')

You can use bind variables:
strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= :1 AND
recordDate < :2 )';


execute immediate strInsert using dtStartDate ,dtEndDate;

that worked like a charm!!!

Thanks a lot!!!...

if i had more input variables needed, i can just add :1, :2, :3 etc?

you are welcome.

yes sure you can use more variables

Thanks alot!