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  gt  s stock minimo  THEN         SET valido   1      ELSE         SET valido   -1      END IF      SELECT valido  END

User · Accepted Answer

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.

User · Answer

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  gt  s stock minimo  THEN     SET valido   1  ELSE     SET valido   -1  END IF  SELECT valido  END

User · Answer

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  gt  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

[mysql] MySQL stored procedure return value

Examples related to mysql

Examples related to stored-procedures

Examples related to return-value