On 4-5-2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com [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