MySQL Stored Procedure returns 0 in parameters from a SELECT

Question

I created a Stored Procedure in MySQL to return some parameters from a SELECT, but doesn't work.

ever returns 0 in userID and otherID.

Someone can help me? Thanks

DROP PROCEDURE IF EXISTS sp_XPTO;

DELIMITER ;;

CREATE PROCEDURE sp_XPTO(

OUT userID INT UNSIGNED,

OUT otherID INT UNSIGNED,

IN bookID INT UNSIGNED

)

BEGIN

SELECT userID=E.userID, otherID=E.otherID

FROM Exp as E

WHERE E.bookID = bookID;

END ;;

DELIMITER ;

UPDATED:

Sorry, After I read a lot, I concluded that the error is in how I call the stored procedure, and Ravinder is correct. Thanks

Ravinder Reddy Ravinder Reddy · Accepted Answer · 2014-04-11T16:03:03

I am not sure if your intention is to check the existence of a value in database.
Because, your select statement is doing a comparison but not assignment.

SELECT userID=E.userID, otherID=E.otherID

With the above statement, you may be thinking that value of E.userID is assigned to the OUT parameter userID. But the syntax you used is wrong.

In the above statement, both of the expressions return a boolean. As userID value did not match with E.userID the comparison resulted a false and you saw a 0. And it was the same for the otherID variable.

To assign values to variables you have to use INTO instructional operator.

Example:

SELECT E.userID, E.otherID 
  INTO userID,   otherID

Change your stored procedure as below:

DROP PROCEDURE IF EXISTS sp_XPTO;

DELIMITER //

CREATE PROCEDURE sp_XPTO(
   OUT userID INT UNSIGNED,
   OUT otherID INT UNSIGNED,
    IN bookID INT UNSIGNED
)
BEGIN
   SELECT E.userID, E.otherID
     INTO userID,   otherID
   FROM Exp as E
   WHERE E.bookID = bookID;  -- <- there was a ')'. removed.
END;
//

DELIMITER ;

Call the procedure with desired parameters and read the out parameters.

call sp_XPTO( @userID, @otherID, 234 ); -- where book id is 234
select @userID, @otherID;

MySQL Stored Procedure returns 0 in parameters from a SELECT

1 Answers