On 4-5-2018 18:13, Steve Naidamast blackfalconsoftware@...
[firebird-support] wrote:

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

> Nonetheless, would you suggest that I put the "SUSPEND" statement within the FOR-DO construct?

The SUSPEND is already in the FOR-DO construct here. What you are
missing is that the above is equivalent to

FOR ... DO
BEGIN
SUSPEND;
END

In other words, it means "for each row do a suspend", while in your
initial question you had

FOR ... DO
IF (PI_KEY_IN = 0) THEN
EXCEPTION ROOT_CAT_NODE_DELETE;
SUSPEND;

which is equivalent to

FOR ... DO
BEGIN
IF (PI_KEY_IN = 0) THEN
EXCEPTION ROOT_CAT_NODE_DELETE;
END
SUSPEND;

which means "for each row do throw an exception if PI_KEY_IN = 0, and
afterwards suspend a single row"

SUSPEND returns the current values of the output fields, and waits for
them to be fetched by the client. See also
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-suspend

Mark
--
Mark Rotteveel