At 11:37 PM 22/12/2004 +0000, you wrote:

>I'm wondering if it is safe to use a local variable as both the input
>and the output variable to a stored procedure?
>
>ie) Inside a stored proc. I call a couple embedded procedures that
>could set a key or not and then return the new value
>
>CREATE PROCEDURE SP_TEST
>RETURNS (
> USEDKEY_NO INTEGER)
>AS
>DECLARE VARIABLE LUSEDKEY_NO INTEGER;
>BEGIN
> LUSEDKEY_NO = NULL;
>
> EXECUTE PROCEDURE SP_TEST2(:LUSEDKEY_NO)
> RETURNING_VALUES(:LUSEDKEY_NO);
>
> EXECUTE PROCEDURE SP_TEST3(:LUSEDKEY_NO)
> RETURNING_VALUES(:LUSEDKEY_NO);
>
> USEDKEY_NO = LUSEDKEY_NO;
>END
>
>so if test2 used the key, then it would set and return it and then it
>could be used in the next stored proc.
>
>I couldn't find any reason why not but just wanted to make sure before
>I start using code like that.

Seems like too much for too little!! SP arguments behave just like
variables inside the PSQL module. Just do this:

CREATE PROCEDURE SP_TEST
RETURNS (
USEDKEY_NO INTEGER)
AS
BEGIN
EXECUTE PROCEDURE SP_TEST2 /* no inputs, assume null always */
RETURNING_VALUES(:USEDKEY_NO);

EXECUTE PROCEDURE SP_TEST3(:USEDKEY_NO)
RETURNING_VALUES(:USEDKEY_NO);
END