[firebird-support] Re: EXECUTE STATEMENT first try issues

[venussoftop]

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