>If we are going the EXECUTE BLOCK approach, which I agree would be better.
>
>Then, the optimal logic would be:
>
> execute block returns (id integer) as
> declare variable Next_ID integer;
> begin
> ID = 1;
> FOR
> SELECT ID
> FROM Table
> ORDER BY ID
> INTO
> :Next_ID
> DO
> Next_ID = Next_ID - 1;
> WHILE (Next_ID >= ID) DO BEGIN
> SUSPEND;
> ID = ID +1;
> END
> ID = ID +1;
> end

Brilliant, Sean!

Though I would suggest some minor refinement:

EXECUTE BLOCK RETURNS (id integer) AS
DECLARE VARIABLE Next_ID integer;
BEGIN
ID = 1;
FOR
SELECT ID
FROM Table
ORDER BY ID
INTO
:Next_ID
DO
BEGIN
WHILE (Next_ID > ID) DO BEGIN
SUSPEND;
ID = ID +1;
END
ID = ID +1;
END
END

or even:

EXECUTE BLOCK RETURNS (id integer) AS
DECLARE VARIABLE Next_ID integer;
BEGIN
ID = 1;
SELECT MIN(ID)
FROM Table
INTO
:Next_ID;
WHILE (Next_To > ID) DO
BEGIN
SUSPEND;
ID = ID +1;
END
FOR
WITH TMP (ID) AS /*TMP.ID will contain first record of gap*/
(SELECT TF.ID+1
FROM Table TF
LEFT JOIN Table TFN ON TF.ID + 1 = TFN.ID
WHERE TFN.ID IS NULL)

SELECT T.ID, MIN(TT.ID) /*TT.ID is first record after end of gap)
FROM TMP T
JOIN Table TT ON TF.ID < TT.ID
GROUP BY 1
INTO
:ID, :Next_ID
DO
BEGIN
WHILE (Next_ID > ID) DO
BEGIN
SUSPEND;
ID = ID +1;
END
END
END

Though this is more complicated logic and I am uncertain to which extent it benefits performance-wise.

<The rest is off topic>

>> Once again, your keen I found an error!
>
>I swear, English is my mother tongue. Although, from the above, you'd never know it!
>
>Once again, your keen *eye* found an error!

Ah, at first I wondered whether it meant 'your keen self found an error', referring to my irritating skill to notice minor mistakes everywhere.

I'd say the difference between a foreigner and a native English speaker is that some foreigners always do the same mistake, natives tend to do mistakes like this occasionally. I typically have a problem with the difference between RISE and RAISE, don't know whether I would have had less problems if English had been my native language. Also, I have observed one similar, but opposite, phenomena in Norwegian: AND and TO are pronounced identically, but spelt differently, and many Norwegians often write the wrong one. I think immigrants learning Norwegian are less prone to this mistake, though once they get fluent, some of them also start having this problem!

Set