> I recently wanted to make a flexible SP that will allow me to change the way the return string is generated. I have pasted my work below but when executing it seems to be getting error on the word INTO as follows:
> Executing...
> Error: *** IBPP::SQLException ***
> Context: Statement::Execute( EXECUTE PROCEDURE RNarr(185, 295)
> )
> Message: isc_dsql_execute2 failed
>
> SQL Message : -104
> Invalid token
>
> Engine Code : 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 1, column 216
> INTO
>
>
> Total execution time: 0.015s
>
>
> My SP:
> SET TERM ^ ;
> ALTER PROCEDURE RNARR (
> TITRID ID,
> TISRFID ID,
> TCPROMPT CHAR250 DEFAULT NULL )
> RETURNS (
> TCNAME VARCHAR(4096) )
> AS
> DECLARE VARIABLE lcSQL VARCHAR(4096);
> BEGIN
> lcSQL = 'SELECT LIST(' || COALESCE(tcPrompt, 'TRIM(mA.cName)') || ', '', '') 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 '
> || 'INTO :tcName';
>
> EXECUTE STATEMENT lcSQL;
> SUSPEND;
> END^
> SET TERM ; ^

Use the INTO part outside of the dynamic SQL string.

E.g.

EXECUTE STATEMENT lcSQL INTO :tcName;


HTH.


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!