CODEWITHSUNDEEP
sqlstored-proceduresdb2return-value
molasses
molasses

Reputation: 3318

Retrieve return value from DB2 stored procedure

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?

Upvotes: 3

Views: 25313

Answers (2)

molasses
molasses

Reputation: 3318

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. :)

Upvotes: 5

Frans Bouma
Frans Bouma

Reputation: 8357

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 ;)

Upvotes: 0

Related Questions