Subject Re: [firebird-support] Re: Problem when returning values with EXECUTE STATEMENT
Author Helen Borrie
At 12:13 AM 21/11/2009, you wrote:

> The firebird 1.5.5 release notes does not give an example on how to
>retrive returning values from non selectable stored procedures. I'm not
>sure if firebird accepts returning_values as a valid statement in the
>dynamic SQL part.

If it had been possible at all, it would have been because EXECUTE STATEMENT had been implemented with an output structure for EXECUTE PROCEDURE. If that were the case, then the syntax I gave you would have "worked" and the PSQL side would have been able to read a valid RETURNING_VALUES structure ( a comma-separated list of colon-prefixed variables inside parentheses) into the INTO variables.

However, it's not surprising that it didn't work. The purpose of EXECUTE STATEMENT is to allow immediate execution of a DSQL statement from within a PSQL module. RETURNING_VALUES() is not a valid construct in DSQL.

Selecting from a selectable SP, viz., one that is defined with SUSPEND for each output row, *is* valid DSQL, so I'm afraid that is what you are stuck with. Just watch carefully that the "real life" output from that SELECT statement cannot return multiple rows INTO your variable list, or you will get a different kind of exception.