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),
    DESC_USUARIO varchar(50),
    SETOR varchar(30),
    CARGO varchar(30))
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,
                            IIF( CHAR_LENGTH(COALESCE(U.NOME,'''')) > 0, U.NOME, U.USUARIO) AS DESC_USUARIO,
                            U.SETOR,
                            U.CARGO
                        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, :DESC_USUARIO, :SETOR, :CARGO
  DO
  BEGIN
    SUSPEND;
  END
END
^

SET TERM ; ^