TareKhoury TareKhoury - 6 months ago 41
MySQL Question

MySQL Stored Procedure return value without a SELECT

I have written the following stored procedure:

DELIMITER $$
CREATE PROCEDURE `getroom`(OUT `free_room` INT)
BEGIN

SELECT room INTO free_room FROM admins WHERE free = 1 LIMIT 1 FOR UPDATE;
UPDATE admins SET free = 0 WHERE room = free_room;

END$$
DELIMITER ;


I am trying to query the table for free rooms, lock the row so other users cannot query it at the same time, and set it as not free at the end.

My questions are:

1) How can I call a stored procedure and get the free_room value automatically (as if i ran a SELECT statement) without doing an additional SELECT statement?

2) Since I am not passing any arguments (don't have "IN" values), why can't I run
CALL getroom()
succssfully? I get the following error:


Incorrect number of arguments for PROCEDURE getroom; expected 1, got 0


Hope my questions are clear enough!

Answer

You can create function instead:

DELIMITER $$
CREATE FUNCTION `getroom`() RETURNS INT
BEGIN
    DECLARE free_room INT;
    SELECT room INTO free_room FROM admins WHERE free = 1 LIMIT 1 FOR UPDATE;
    UPDATE admins SET free = 0 WHERE room = free_room;
    RETURN free_room;
END$$
DELIMITER ;

and then just use:

SELECT getroom();
Comments