How can I solve this problem in a procedure stored in MYSQL?

Question

How can I solve this problem in a procedure stored in MYSQL?

Navigation

#1 by (0 votes)

1

Greetings to the community I have this error in the creation of this procedure stored in MYSQL

  DELIMITER $$
CREATE PROCEDURE insertarnuevo15(
IN Vnumfact BIGINT(20),
IN Vserfact BIGINT(20)
)
BEGIN
  DECLARE Vnumfact BIGINT(20);
  SET Vnumfact = 0;

IF(SELECT serfact FROM factura WHERE serfact = Vserfact)> 0 THEN
     SELECT MAX(numfact)
     FROM factura
     WHERE serfact = Vserfact
     INTO vnumfact;
     SET Vnumfact = Vnumfact + 1;  
ELSE IF Vserfact = 0 THEN
    SELECT MAX(serfact)
    FROM factura
    INTO Vserfact;
    SET Vserfact = Vserfact + 1;
END IF;
END IF;
INSERT INTO factura (numfact,serfact) VALUES(Vnumfact, Vserfact);
  SELECT CONCAT('F',LPAD(Vserfact, 4, '0'), '-',LPAD(Vnumfact, 6, '0')) serial;

 END$$

Now I have corrected the previous error SHOWS me when calling the stored procedure.

CALL 'prueba3'.'insertarnuevo'(1);

and when the respective select to the invoice table shows me the following and does not insert in numfact

codigo   numfact  serfact
  1         0       1

generates the serial number: F0001 000000

but should generate F0001 000001 thank you very much

mysql mysql-workbench

asked by Libra2880 11.10.2018 в 04:19

source

1 answer

Alias in virtual host in project Xampp in Laravel 5.6 Compare two dates with YYYY-mm-dd format in javascript

score 0 · Accepted Answer

I leave the solution

DELIMITER $$
CREATE PROCEDURE 'insertarnuevo1'(
IN Vnumfact BIGINT(20),
IN Vserfact BIGINT(20)
)
BEGIN


IF(SELECT serfact FROM factura WHERE serfact = Vserfact)> 0 THEN
     SELECT MAX(numfact)
     FROM factura
     WHERE serfact = Vserfact
     INTO Vnumfact;
     SET Vnumfact = Vnumfact + 1;  
ELSE
IF Vserfact = 0 THEN
    SELECT MAX(serfact)
    FROM factura
    INTO Vserfact;
    SET Vserfact = Vserfact + 1;
END IF;
end if;
INSERT INTO factura (numfact,serfact)
  VALUES(Vnumfact, Vserfact);
  SELECT CONCAT('F',LPAD(Vserfact, 4, '0'), '-',LPAD(Vnumfact, 6, '0')) serial;

 END$$