SET TERM ^ ;

CREATE OR ALTER procedure SPC_USUARIOS_EXTERNOS (
    SERVIDOR varchar(50),
    BANCO varchar(50),
    USUARIOI varchar(50),
    SENHA varchar(50))
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
                            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'''
      ON EXTERNAL :SERVIDOR||'/3050:'||:BANCO
      WITH AUTONOMOUS TRANSACTION
      AS USER :USUARIOI PASSWORD :SENHA
      INTO :ID, :DESC_ID, :USUARIO, :NOME, :DESC_USUARIO, :SETOR, :CARGO
  DO
  BEGIN
    SUSPEND;
  END
END
^

SET TERM ; ^