Dimitry ...
Thank you for your reply... 😊 However, I am not sure about the multiple "SUSPEND" statements on a per-record basis. If you look at my procedure below, you will note that there is only a single "SUSPEND" statement. Yet, all of the rows for the entered date that is used to execute the procedure (select statement follows module code) are returned as expected (9 rows returned)... >>> >>> Stored Procedure >>> CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES( PS_DATE_IN VARCHAR(10) NOT NULL) RETURNS( PS_DATE_OUT VARCHAR(10) NOT NULL) AS DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL; BEGIN PS_SQL = 'SELECT DISTINCT'; PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG'; PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM ML_CREATE_DATE) AS VARCHAR(2)))' || '/'; PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(DAY FROM ML_CREATE_DATE) AS VARCHAR(2)))' || '/'; PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(YEAR FROM ML_CREATE_DATE) AS VARCHAR(4)))) = ' || :PS_DATE_IN; FOR EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT DO SUSPEND; END; <<< >>> >>> SELECT Statement >>> SELECT * FROM SP_GET_MSGLOG_RECS ('10/17/2017') <<< Nonetheless, would you suggest that I put the "SUSPEND" statement within the FOR-DO construct? Thank you... Steve Naidamast Sr. Software Engineer blackfalconsoftw...@outlook.com [cid:96714d12-5078-4b55-a660-b7d6d4c1b734] ________________________________ From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] <firebird-support@yahoogroups.com> Sent: Thursday, May 3, 2018 5:17 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure 03.05.2018 23:04, blackfalconsoftw...@outlook.com [firebird-support] wrote: > However, I though only the issuance one "SUSPEND" was enough to flush the > entire buffer of > all records... No. One SUSPEND - one record in result set. Two SUSPENDs - two records and so on. Watch this in isql: SQL> set term GO; SQL> EXECUTE BLOCK RETURNS (A INTEGER) CON> AS CON> begin CON> a = 1; CON> SUSPEND; -- return a record containing 1 CON> a = 2; CON> SUSPEND; -- return a record containing 2 CON> SUSPEND; -- return another record containing 2 CON> end CON> GO A ============ 1 2 2 -- WBR, SD. ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item Firebird: The true open source database for Windows, Linux ...<http://www.firebirdsql.org/> www.firebirdsql.org Firebird SQL: The true open-source relational database on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links [Non-text portions of this message have been removed]