Retrieve return value from DB2 stored procedure

Question

I have a block of code that is repeated within a DB2 stored procedure. I would like to separate this out into a new procedure that I can call with parameters and have it return a value.

How do I create a procedure to return a value and how do I call this procedure from inside my original procedure?

Answer 1

How about an output parameter in the proc you call from within your original proc? Calling a proc is done through the CALL command. It's in the manual ;)

Answer 2

Yes, an output parameter is all it took. I couldn't find the right calling syntax in the manual or google though.

You create the procedure like this:

CREATE PROCEDURE myschema.add(IN a INT, IN b INT, OUT c INT)
BEGIN
    SET c = a + b;
END

And then call it like this (this is what I couldn't figure out):

DECLARE result INT DEFAULT 0;

CALL myschema.add(10, 20, result);

-- result == 30

And then the output ends up in the supplied result variable. You can also have multiple OUT params as well as INOUT params.

Sure it seems obvious now. :)