MySQL Stored Procedure return value without a SELECT

Question

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!

ad 2) note that the procedure really has one parameter - the one over which the result is returned. So, you need to call the procedure with CALL getroom(@somevar). Afterwards, the result will be available in @somevar. — EagleRainbow
– EagleRainbow, Commented May 22, 2016 at 8:30
Ok, so the parameter is needed for the OUT value. For my first question, is there a way to CALL getroom(@somevar) and get a return value without having to do CALL getroom(@somevar); SELECT @somevar;? — TareK Khoury
– TareK Khoury, Commented May 22, 2016 at 8:35
try to create a FUNCTION having return statement to achieve the same. — Ankit Bajpai
– Ankit Bajpai, Commented May 22, 2016 at 9:47

piotrgajow · Accepted Answer · 2016-05-22 10:22:05Z

1

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();

answered May 22, 2016 at 10:22

piotrgajow

2,9501 gold badge24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TareK Khoury Over a year ago

This did the job! will it also row lock so it cannot be read by other users while it is updating?

piotrgajow Over a year ago

Yes, the SELECT FOR UPDATE should lock the row, until it is updated with the next UPDATE statement.

wchiquito · Accepted Answer · 2016-05-22 10:27:45Z

0

Try:

DELIMITER $$

CREATE PROCEDURE `getroom`()
BEGIN 
    SELECT @free_room := room
    FROM admins
    WHERE free = 1
    LIMIT 1
    FOR UPDATE;

    UPDATE admins
    SET free = 0
    WHERE room = @free_room;   
END$$

DELIMITER ;

answered May 22, 2016 at 10:27

wchiquito

16.7k2 gold badges37 silver badges46 bronze badges

Comments

score 0 · Accepted Answer · 2016-05-23 00:44:25Z

0

DELIMITER $$
CREATE PROCEDURE `getroom`()
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;
    SELECT free_room;
END$$
DELIMITER ;

edited May 23, 2016 at 0:44

answered May 23, 2016 at 0:36

user2560539

Collectives™ on Stack Overflow

MySQL Stored Procedure return value without a SELECT

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related