--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:

> Use the INTO part outside of the dynamic SQL string.
>
> E.g.
>
> EXECUTE STATEMENT lcSQL INTO :tcName;

Thanks a lot Thomas. I took it bit further as below, looks more flexible. Suggestions welcome.

Thanks again

Kind regards
Bhavbhuti

eg:
EXECUTE PROCEDURE RECEIPTNARRATIONACCOUNTS(185, 294, NULL, 'ASCII_CHAR(13)')


SP:
SET TERM ^ ;
ALTER PROCEDURE RECEIPTNARRATIONACCOUNTS (
TITRID ID,
TISRFID ID,
TCPROMPT CHAR250 DEFAULT NULL,
TCSEP CHAR250 DEFAULT NULL )
RETURNS (
TCNAME BLOB SUB_TYPE 0 )
AS
DECLARE VARIABLE lcSQL VARCHAR(4096);
BEGIN
lcSQL = 'SELECT LIST(' || COALESCE(tcPrompt, 'TRIM(mA.cName)') || ', ' || COALESCE(tcSep, ''', ''') || ') AS cName '
|| 'FROM tReceipt tR '
|| ' JOIN sReceiptFooter sRF '
|| ' ON sRF.iPID = tR.iID '
|| ' JOIN mAccounts mA '
|| ' ON mA.iID = sRF.iAccountID '
|| 'WHERE tR.iID = ' || titRID || ' AND sRF.iID <> ' || tisRFID ;

EXECUTE STATEMENT lcSQL INTO :tcName;
SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE RECEIPTNARRATIONACCOUNTS TO SYSDBA;