MySQL stored procedure return value

Question

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 

Answer 1

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.

Answer 2

Add:

• DELIMITER at the beginning and end of the SP.
• DROP PROCEDURE IF EXISTS validar_egreso; at the beginning
• When calling the SP, use @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;