I have to create an SP that returns a value if it's valid or not. But it doesn't return anything and I don't know, why?
CREATE DEFINER=`root`@`localhost` PROCEDURE `validar_egreso`(
IN codigo_producto VARCHAR(100),
IN cantidad INT,
OUT valido INT(11)
)
BEGIN
DECLARE resta INT(11);
SET resta = 0;
SELECT (s.stock - cantidad) INTO resta
FROM stock AS s
WHERE codigo_producto = s.codigo;
IF (resta > s.stock_minimo) THEN
SET valido = 1;
ELSE
SET valido = -1;
END IF;
SELECT valido;
END
This question is related to
mysql
stored-procedures
return-value
You have done the stored procedure correctly but I think you have not referenced the valido variable properly. I was looking at some examples and they have put an @ symbol before the parameter like this @Valido
This statement SELECT valido; should be like this SELECT @valido;
Look at this link mysql stored-procedure: out parameter. Notice the solution with 7 upvotes. He has reference the parameter with an @ sign, hence I suggested you add an @ sign before your parameter valido
I hope that works for you. if it does vote up and mark it as the answer. If not, tell me.
Update your SP and handle exception in it using declare handler with get diagnostics so that you will know if there is an exception. e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `validar_egreso`(
IN codigo_producto VARCHAR(100),
IN cantidad INT,
OUT valido INT(11)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1, @p2;
END
DECLARE resta INT(11);
SET resta = 0;
SELECT (s.stock - cantidad) INTO resta
FROM stock AS s
WHERE codigo_producto = s.codigo;
IF (resta > s.stock_minimo) THEN
SET valido = 1;
ELSE
SET valido = -1;
END IF;
SELECT valido;
END
Add:
DELIMITER at the beginning and end of the SP.validar_egreso; at the beginning@variableName.This works for me. (I modified some part of your script so ANYONE can run it with out having your tables).
DROP PROCEDURE IF EXISTS `validar_egreso`;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE `validar_egreso` (
IN codigo_producto VARCHAR(100),
IN cantidad INT,
OUT valido INT(11)
)
BEGIN
DECLARE resta INT;
SET resta = 0;
SELECT (codigo_producto - cantidad) INTO resta;
IF(resta > 1) THEN
SET valido = 1;
ELSE
SET valido = -1;
END IF;
SELECT valido;
END $$
DELIMITER ;
-- execute the stored procedure
CALL validar_egreso(4, 1, @val);
-- display the result
select @val;
Source: Stackoverflow.com