On 4-5-2018 18:13, Steve Naidamast [email protected]
[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
