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
blackfalconsoftware@...
[cid:96714d12-5078-4b55-a660-b7d6d4c1b734]






________________________________
From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on behalf of Dimitry Sibiryakov sd@... [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, blackfalconsoftware@... [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]