How I can exit within a procedure

[Luigi Siciliano]

Hello,

  I have a procedure like this:

FOR SELECT
    r.SCADENZA,
    SUM(- r.IMPORTO) + :SALDO
  FROM
    VISTA_ELENCO_SCADENZE_FORNITORI r
  WHERE
    r.scadenza >= :DATADAL
  GROUP BY
    r.SCADENZA
  INTO
    :SCADENZA,
    :SALDO
  DO SUSPEND;

I want to stop execution if :SALDO < 0.00 and return all rows affected
because I want all rows with Saldo >= 0 and the first row with :Saldo <
0.00.

How I do?

Thanks

--
Luigi

[Dimitry Sibiryakov]

Luigi Siciliano wrote 27.07.2026 16:43:
> How I do?

Use EXIT after SUSPEND.

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-psql-coding.html#fblangref50-psql-exit


--
WBR, SD.

[Luigi Siciliano]

Il 27/07/2026 16:46, 'Dimitry Sibiryakov' via firebird-support ha scritto:
> Use EXIT after SUSPEND.
>
> https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-psql-coding.html#fblangref50-psql-exit
>

If I modify my procedure like this:

FOR SELECT
    r.SCADENZA,
    SUM(- r.IMPORTO) + :SALDO
  FROM
    VISTA_ELENCO_SCADENZE_FORNITORI r
  WHERE
    r.scadenza >= :DATADAL
  GROUP BY
    r.SCADENZA
  INTO
    :SCADENZA,
    :SALDO
  DO
      SUSPEND;

    BEGIN
      if (SALDO < 0.00) THEN  EXIT;
    end

I obtain all rows regardless that :SALDO > 0.00 or :Saldo < 0.00.

If I modify my procedure Like this:

FOR SELECT
    r.SCADENZA,
    SUM(- r.IMPORTO) + :SALDO
  FROM
    VISTA_ELENCO_SCADENZE_FORNITORI r
  WHERE
    r.scadenza >= :DATADAL
  GROUP BY
    r.SCADENZA
  INTO
    :SCADENZA,
    :SALDO
  DO

    BEGIN
      if (SALDO < 0.00) THEN  EXIT;
      SUSPEND;
    end

I obtain only rows that :Saldo > 0.00

If I modify the procedure like this:

FOR SELECT
    r.SCADENZA,
    SUM(- r.IMPORTO) + :SALDO
  FROM
    VISTA_ELENCO_SCADENZE_FORNITORI r
  WHERE
    r.scadenza >= :DATADAL
  GROUP BY
    r.SCADENZA
  INTO
    :SCADENZA,
    :SALDO
  DO

    BEGIN
      if (SALDO < 0.00) THEN
      SUSPEND;
    end

I obtain only rows that :SALDO < 0.00

What I'm wrong?

Thanks.

--
Luigi

[Dimitry Sibiryakov]

Luigi Siciliano wrote 27.07.2026 17:34:
> What I'm wrong?

Read once again: "use EXIT *after* SUSPEND":

DO
BEGIN
SUSPEND;

if (SALDO < 0.00) THEN EXIT;
end

--
WBR, SD.

[Mark Rotteveel]

On 27-07-2026 17:34, Luigi Siciliano wrote:
> If I modify my procedure like this:
>
> FOR SELECT

...

>   DO
>       SUSPEND;
>     BEGIN
>       if (SALDO < 0.00) THEN  EXIT;
>     end
>
> I obtain all rows regardless that :SALDO > 0.00 or :Saldo < 0.00.

That is because as written that BEGIN / END block is not related to the
FOR SELECT, but a top-level standalone block that is executed *after*
your FOR SELECT completes.

After the DO must follow either a *single* statement (the SUSPEND in
your code) _or_ a BEGIN/END block.

So, you need to use:

FOR SELECT ...
DO
BEGIN
SUSPEND;

if (SALDO < 0.00) THEN EXIT;

END

> If I modify my procedure Like this:
>
> FOR SELECT

...

>   DO
>     BEGIN
>       if (SALDO < 0.00) THEN  EXIT;
>       SUSPEND;
>     end
>
> I obtain only rows that :Saldo > 0.00

That is because you exit before that row with SALDO < 0.00 is output
(that is what SUSPEND does).

> If I modify the procedure like this:
>
> FOR SELECT

...

>   DO
>     BEGIN
>       if (SALDO < 0.00) THEN
>       SUSPEND;
>     end
>
> I obtain only rows that :SALDO < 0.00

That is because now you SUSPEND (output) *all* rows that are >= 0.00,
and don't exit at the first row that is < 0.00.

Mark
--
Mark Rotteveel

[Luigi Siciliano]

Il 27/07/2026 19:28, 'Mark Rotteveel' via firebird-support ha scritto:
> That is because as written that BEGIN / END block is not related to
> the FOR SELECT, but a top-level standalone block that is executed
> *after* your FOR SELECT completes.
>
> After the DO must follow either a *single* statement (the SUSPEND in
> your code) _or_ a BEGIN/END block.

> That is because you exit before that row with SALDO < 0.00 is output
> (that is what SUSPEND does).

> That is because now you SUSPEND (output) *all* rows that are >= 0.00,
> and don't exit at the first row that is < 0.00.

Thank you, Mark, for your helpful explanation.

--
Luigi