syntax mysql procedure

Question

syntax mysql procedure

Navigation

#1 by (0 votes)

1

Could you please help me with the following procedure in mysql? ... The following procedure is in sqlserver. I would appreciate if you would indicate what it would be like with your syntax. I would appreciate your help please, I am suffering days ago:

CREATE PROCEDURE REGISTRARVENTA(
@idoperacion INT ,
@id_producto INT,
@fecha DATE,
@cantidad DECIMAL(7,2),
@estado VARCHAR(25),
@precio DECIMAL(7,2),
@stock DECIMAL(7,2),
@venta DECIMAL(7,2),
@MENSAJE VARCHAR(50) OUTPUT)
    AS
BEGIN   
    IF(EXISTS(SELECT * FROM operacion WHERE idoperacion =@idoperacion))
SET @MENSAJE ='VENTA YA  EXISTE'
    ELSE
IF(EXISTS(SELECT * FROM producto WHERE STOCK<@CANTIDAD))
    SET @MENSAJE ='NO HAY SUFICIENTE STOCK'
    BEGIN
INSERT operacion VALUES(@id_producto , @fecha , @cantidad, @estado, @precio, @stock )
    UPDATE producto SET STOCK =STOCK - @CANTIDAD WHERE id_producto=@id_producto
    SET @MENSAJE ='VENTA REGISTRADA CORRECTAMENTE'
END 
    END 
go

mysql sql-server

asked by Frank Gastelo 21.07.2018 в 17:42

source

1 answer

Functions in a Preloader Display user data logged in php

score 0 · Accepted Answer

If you check in the official page of mysql they indicate the correct way to generate the Stored Procedure, according to what I read this can serve you, there are some differences in the way to declare the variables and apply the conditionals, but in It is usually easy to make the adaptations in these cases:

DELIMITER //
CREATE PROCEDURE REGISTRARVENTA(
IN idoper INT ,
IN id_produc INT,
IN fecha DATE,
IN cantidad FLOAT(7,2),
IN estado VARCHAR(25),
IN precio FLOAT(7,2),
IN stock FLOAT(7,2),
IN venta FLOAT(7,2),
OUT MENSAJE VARCHAR(50))

BEGIN   
    IF ((SELECT COUNT(*) FROM operacion WHERE idoperacion = idoper) > 0) THEN
        SET MENSAJE ='VENTA YA  EXISTE';
  ELSEIF ((SELECT COUNT(*) FROM producto WHERE STOCK < cantidad) > 0) THEN
        SET MENSAJE ='NO HAY SUFICIENTE STOCK';
    ELSE
            INSERT INTO operacion VALUES(id_produc , fecha , cantidad, estado, precio, stock );
        UPDATE producto SET STOCK = STOCK - cantidad WHERE id_producto = id_produc;
        SET MENSAJE ='VENTA REGISTRADA CORRECTAMENTE';
    END IF;
END //
DELIMITER ;

I hope it serves you.