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

Use [exception handling](http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html) in your SP. — Rishi Vedpathak, Nov 05 '14 at 16:56

score 28 · Accepted Answer · edited Jul 19 '17 at 08:39

28

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.

edited Jul 19 '17 at 08:39

Noam M

3,156
5
26
41

answered Nov 05 '14 at 16:04

mfredy

597
1
8
16

2

Putting @ infront of the variable makes it a session-specific user-defined variable, which works fine. You can also declare the variable as `DECLARE valido INT`. – biniam Dec 02 '15 at 15:40
The declaration, passing the OUT parameter, worked out for me. – Fabio Martins Feb 17 '20 at 17:48

biniam · Answer 2 · 2016-10-06T09:52:55.860

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;

score 3 · Answer 3 · answered Nov 05 '14 at 16:55

3

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

answered Nov 05 '14 at 16:55

Rishi Vedpathak

778
2
10
24

What is "GET DIAGNOSTICS CONDITION 1" here ? – Sohel Pathan Sep 12 '17 at 09:45
The MySQL 5.6 parser tells me that the END statement on the SQLEXCEPTION handler block needs a semicolon, and that the DECLARE must precede the block that declares the handler. see "13.6.4.1 Local Variable DECLARE Syntax, " at https://dev.mysql.com/doc/refman/5.7/en/declare-local-variable.html. – David A. Gray Jan 18 '18 at 23:39

MySQL stored procedure return value

3 Answers3

Linked