No rows returned in Firebird for execute statement for procedure in another database

Question

I need to write a procedure which works with the result set of rows from another procedure in a separate database. But each time I call it, I get no rows as though table is empty.

Source procedure

CREATE OR ALTER PROCEDURE get_scanner_active_tags_by_hh_type (
    scannerreferenceid TYPE OF COLUMN scanners.referenceid,
    hourtype TYPE OF COLUMN tagscannerpairings.hourtype)
RETURNS (
    tagid TYPE OF COLUMN tagscannerpairings.tagid)
AS
BEGIN
  FOR SELECT tagid
    FROM tagscannerpairings tsp JOIN scanners so ON tsp.scannerreferenceid = so.referenceid
    WHERE EXISTS (
      SELECT *
      FROM scanners si
      WHERE TRIM(TRAILING FROM si.referenceid) = TRIM(TRAILING FROM tsp.scannerreferenceid) AND si.referenceid = :scannerreferenceid) AND ison = 'T' AND hourtype = :hourtype
      ORDER BY tagid
    INTO :tagid
  DO
    SUSPEND;
END

Procedure of issue

CREATE OR ALTER PROCEDURE write_hh_records (
    scannername vchar30_req,
    hh_type hour_type)
RETURNS (
    tagid INTEGER)
AS
BEGIN
  FOR EXECUTE STATEMENT ('EXECUTE PROCEDURE get_scanner_active_tags_by_hh_type(:scanner, :hh);') (scanner := :scannername,  hh := :hh_type)
    AS USER '<user>'
    PASSWORD '<password>'
    ROLE '<role>'
    ON EXTERNAL 'xx.xx.x.xx/3050:Config'
    INTO :tagid
  DO
    SUSPEND;
END

Answer 1

An EXECUTE PROCEDURE returns at most one row, and as far as I know, you cannot execute it dynamically with FOR EXECUTE STATEMENT, but only with EXECUTE STATEMENT. However, I haven't double-checked that for this answer, and otherwise it will probably return at most one row.

The stored procedure get_scanner_active_tags_by_hh_type returns zero or more rows as a result set, and that means you need to use select tagid from get_scanner_active_tags_by_hh_type(...) to execute it, and then you can execute it dynamically with FOR EXECUTE STATEMENT to get all those rows.

TL;DR: Use

FOR EXECUTE STATEMENT ('select tagid from get_scanner_active_tags_by_hh_type(:scanner, :hh);') (scanner := :scannername,  hh := :hh_type)
  ...