Wrong number or types of parameters in call for cursor while creating procedure.

[former_member184712]

Hi,

I am trying out Alvaro Tejada Galindo tutorial at http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70e2ff67-587c-2f10-5da1-8e5ddbade.... On page 26, there is sample code for creating procedure. I am trying that out but I am getting the above error in the line FOR cur_row AS c_cursor1 DO . I have created everything successfully and also checked the sql script guide. I am unable to find out what mistake I have made. Here is the code below

CREATE PROCEDURE GetPersonYears()

LANGUAGE SQLSCRIPT AS

v_age varchar(10) := '';

v_years varchar(2) := '';

CURSOR c_cursor1(v_age varchar(10)) for

select ID, NAME || ' ' || SURNAME as "NAME",

                    days_between(DOB,now()) as "AGE"

FROM PERSON;

BEGIN

FOR cur_row AS c_cursor1 DO

if cur_row.AGE > 100 then

v_years := floor(cur_row.AGE / 360);

INSERT into EMPLOYEE

values(cur_row.ID,cur_row.NAME,v_years);

end if;

end for;

end;

And here is the code screenshot from the tutorial.

[Former Member · Accepted solution]

Hi Arun,

You just need to pass the parameter into the cursor. For the above code, change the FOR statement to

FOR cur_row AS c_cursor1(v_age) DO

and that should do the trick.

Thanks,

Anooj

[Former Member · Accepted solution]

I tried the below code and it is working fine.

CREATE PROCEDURE GetPersonYears()

LANGUAGE SQLSCRIPT AS

v_age varchar(10) := '';

v_years varchar(2) := '';

v_id varchar(3) :='';

v_dob varchar(8) :='';

CURSOR c_cursor1(v_id varchar(3),v_age varchar(10),v_dob varchar(8)) for

select ID, NAME || ' ' || Last_name as "NAME",

days_between(DOB,now()) as "AGE" FROM PERSON;

BEGIN

FOR cur_row AS c_cursor1(v_id,v_age,v_dob) DO

if cur_row.AGE > 100 then

v_years := floor(cur_row.AGE / 360);

INSERT into EMPLOYEE

values(cur_row.ID,cur_row.NAME,v_years);

end if;

end for;

end;