output parameter as input parameter?

Analytics
Enthusiast

output parameter as input parameter?

I have two stored procedures; sp_A calls sp_B. sp_B has an output parameter, varOutB. After I have called sp_B within sp_A, I'm trying to use varOutB in an INSERT statement in sp_A but I get a message that varOutB is an undefined symbol.

Pseudo SQL below:

REPLACE PROEDURE sp_a
(IN varA INTEGER)
BEGIN
CALL sp_b (:varA, varB);
INSERT INTO T_TEST VALUES(varA, varB);
END;

REPLACE PROCEDURE sp_b
(IN varA INTEGER, OUT varB INTEGER)
BEGIN
{do stuff to calculate varB}
END;

When I call sp_b by itself, I get the result I expect.

call sp_b(1, varB);

varB
2.5

I can run sp_a passing paramter value = 1. However, the inserted values into table T_TEST are only 1, null.

Thoughts, suggestions?

Nolan
3 REPLIES
Enthusiast

Re: output parameter as input parameter?

It looks like you need to define varB in sp_a, using a "DECLARE" statement.

Put the following after your "BEGIN" line in sp_a:

DECLARE varB INTEGER;

See if that works.
Enthusiast

Re: output parameter as input parameter?

I'm still only getting only 1, null inserted into T_TEST.
Enthusiast

Re: output parameter as input parameter?

After I add the DECLARE statement, I get two values inserted into the table instead of a value and NULL. What version are you on? I'm working with 6.0.

Have you tried putting a colon ( :-) in front of your variable names in all of your SQL? (CALL statements and INSERT statement)

I think that you needed a colon in earlier releases.