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 for the help. You are correct. It's working fine after I initialized the output parameter.
Thanks & Regards,
Originally posted by ARWinner
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)
SET dept1 = 0;
FOR v2 AS
SELECT dept FROM staff
SET dept1 = dept1 + dept;
END FOR ;