Hi Friends,

I am new to DB2. I write the simplefor.sql(see attached file) stored procedure. It simply fetches the 'dept' number of all the rows (using for loop) from the staff table and adds to the output parameter 'dept1'. The stored procedure gets successfully created into the SAMPLE database.
When I call the stored procedure using
'db2 CALL SIMPLEFOR\(?\)', the output is displayed as shown below:

Value of output parameters
--------------------------
Parameter Name : DEPT1
Parameter Value : -

Return Status = 0

See, the value is not printed for 'dept1' output parameter.

My M/C: Linux 8.0
My DB2: UDB/DB2 v8.1

Please let me know where I am going wrong or is it DB2's bug?

Thanks & Regards,
Jake

Jake,
The reason is that you never initialized dept1. It will be intiialized to NULL. And then you can probably guess that addition with a null will result in null. Try it this way:


CREATE PROCEDURE SIMPLEFOR(OUT dept1 INT)
LANGUAGE SQL
BEGIN
SET dept1 = 0;
FOR v2 AS
SELECT dept FROM staff
DO
SET dept1 = dept1 + dept;
END FOR ;
END
@


HTH

Andy

Hi Andy,

Thanks for the help. You are correct. It's working fine after I initialized the output parameter.

Thanks & Regards,
Jake