SET TERM ^ ;

CREATE OR ALTER PROCEDURE SPC_USUARIOS_EXTERNOS_V2 (
    QTD INTEGER,
    PAGINA INTEGER,
    SERVIDOR VARCHAR(50),
    BANCO VARCHAR(50),
    USUARIO_DB VARCHAR(50),
    SENHA_DB VARCHAR(50),
    FILTRO VARCHAR(1000) = '')
RETURNS (
    ID INTEGER,
    DESC_ID VARCHAR(10),
    USUARIO VARCHAR(50),
    NOME VARCHAR(50))
AS
BEGIN
  FOR EXECUTE STATEMENT 'SELECT FIRST '||:QTD||' SKIP '||:PAGINA||'
                            U.CODIGO AS ID,
                            FN_LPAD(U.CODIGO, 4, ''0'') AS DESC_ID,
                            U.USUARIO,
                            U.NOME
                        FROM TBUSUARIO U
                        WHERE U.STATUS = ''1'' '||:FILTRO
      ON EXTERNAL :SERVIDOR||'/3050:'||:BANCO
      WITH AUTONOMOUS TRANSACTION
      AS USER :USUARIO_DB PASSWORD :SENHA_DB
      INTO :ID, :DESC_ID, :USUARIO, :NOME
  DO
  BEGIN
    SUSPEND;
  END
END^

SET TERM ; ^