Fórum Commitando uma Stored Procedure! #56744

SET TERM ^ ;

CREATE PROCEDURE MVENDACAB_INS_UPD (
    CONTADOR INTEGER,
    NRDOC CHAR(8),
    MODELO CHAR(8),
    DATA DATE,
    HORAINI TIME,
    HORAFIM TIME,
    CLIENTE CHAR(6),
    VENDEDOR CHAR(6),
    CAIXA CHAR(3),
    ECF CHAR(4),
    MODECF VARCHAR(8),
    ITENS INTEGER,
    TIPODESCONTO CHAR(1),
    DESCONTO VARCHAR(8),
    TOTALDESCONTO VARCHAR(13),
    TOTAL VARCHAR(13),
    TROCO VARCHAR(13),
    FPG1 CHAR(6),
    VLPG1 VARCHAR(13),
    FPG2 CHAR(6),
    VLPG2 VARCHAR(13),
    FPG3 CHAR(6),
    VLPG3 VARCHAR(13),
    MENSAGEM BLOB SUB_TYPE 1 SEGMENT SIZE 384,
    CANCELADO SMALLINT,
    CONCLUIDO SMALLINT)
AS
BEGIN
  IF (EXISTS(SELECT CONTADOR FROM MVENDACAB WHERE (CONTADOR = :CONTADOR))) THEN
    UPDATE MVENDACAB
    SET
    CONTADOR = :CONTADOR,
    NRDOC = :NRDOC,
    MODELO = :MODELO,
    DATA = :DATA,
    HORAINI = :HORAINI,
    HORAFIM = :HORAFIM,
    CLIENTE = :CLIENTE,
    VENDEDOR = :VENDEDOR,
    CAIXA = :CAIXA,
    ECF = :ECF,
    MODECF = :MODECF,
    ITENS = :ITENS,
    TIPODESCONTO = :TIPODESCONTO,
    DESCONTO = :DESCONTO,
    TOTALDESCONTO = :TOTALDESCONTO,
    TOTAL = :TOTAL,
    TROCO = :TROCO,
    FPG1 = :FPG1,
    VLPG1 = :VLPG1,
    FPG2 = :FPG2,
    VLPG2 = :VLPG2,
    FPG3 = :FPG3,
    VLPG3 = :VLPG3,
    CANCELADO = :CANCELADO,
    CONCLUIDO = :CONCLUIDO
    WHERE (CONTADOR = :CONTADOR);
  ELSE
    INSERT INTO MVENDACAB(
    CONTADOR,
    NRDOC,
    MODELO,
    DATA,
    HORAINI,
    HORAFIM,
    CLIENTE,
    VENDEDOR,
    CAIXA,
    ECF,
    MODECF,
    ITENS,
    TIPODESCONTO,
    DESCONTO,
    TOTALDESCONTO,
    TOTAL,
    TROCO,
    FPG1,
    VLPG1,
    FPG2,
    VLPG2,
    FPG3,
    VLPG3,
    CANCELADO,
    CONCLUIDO
)
    VALUES (
    :CONTADOR,
    :NRDOC,
    :MODELO,
    :DATA,
    :HORAINI,
    :HORAFIM,
    :CLIENTE,
    :VENDEDOR,
    :CAIXA,
    :ECF,
    :MODECF,
    :ITENS,
    :TIPODESCONTO,
    :DESCONTO,
    :TOTALDESCONTO,
    :TOTAL,
    :TROCO,
    :FPG1,
    :VLPG1,
    :FPG2,
    :VLPG2,
    :FPG3,
    :VLPG3,
    :CANCELADO,
    :CONCLUIDO
);

END
^

SET TERM ; ^

GRANT SELECT,INSERT,UPDATE ON MVENDACAB TO PROCEDURE MVENDACAB_INS_UPD;

GRANT EXECUTE ON PROCEDURE MVENDACAB_INS_UPD TO SYSDBA;

  with DMVL.SPVendeCab do
    begin
      try
        Params[0].AsInteger := id;
        Params[1].AsString := copy(S1, 1, 8);
        Params[2].AsString := copy(S2, 1, 8);
        Params[3].AsDate := D1;
        Params[4].AsTime := T1;
        Params[5].AsTime := T2;
        Params[6].AsString := copy(S3, 1, 6);
        Params[7].AsString := copy(S4, 1, 6);
        Params[8].AsString := copy(S5, 1, 3);
        Params[9].AsString := copy(S6, 1, 4);
        Params[10].AsString := copy(S7, 1, 8);
        Params[11].AsInteger := I1;
        Params[12].AsString := copy(S8, 1, 1);
        Params[13].AsString := copy(S9, 1, 8);
        Params[14].AsString := copy(S10, 1, 13);
        Params[15].AsString := copy(S11, 1, 13);
        Params[16].AsString := copy(S12, 1, 13);
        Params[17].AsString := copy(S13, 1, 6);
        Params[18].AsString := copy(S14, 1, 13);
        Params[19].AsString := copy(S15, 1, 6);
        Params[20].AsString := copy(S16, 1, 13);
        Params[21].AsString := copy(S17, 1, 6);
        Params[22].AsString := copy(S18, 1, 13);
        Params[23].AsString := copy(S19, 1, 384);
        Params[24].AsInteger := I2;
        Params[25].AsInteger := I3;

        ExecProc;
      except
        Application.MessageBox(´Erro na gravação das informações iniciais!´, ´Atenção´, MB_ICONINFORMATION);
      end;
    end;