Mysql procedure increment variable

Question

I'm trying to get rank of user in the table with stored time.

RAW SQL query is working fine but I can't make it work as procedure.

SET @rownum := 0;
SELECT rank, user_id, best_time
FROM (
  SELECT @rownum := @rownum +1 AS rank,id, best_time, user_id
  FROM user_round WHERE round_id=1 ORDER BY best_time ASC
) AS result WHERE user_id = 1

My try to procedure:

BEGIN
 DECLARE variable INT DEFAULT 0;
 SELECT rank,best_time, user_id
 FROM (
  SELECT SET variable=variable+1 AS rank, best_time, user_id
  FROM database.user_round WHERE round_id=1 ORDER BY best_time ASC
 ) AS result WHERE user_id = 1;
END

Try removing SET from SELECT SET variable=variable+1

Aziz Shaikh
– Aziz Shaikh

2013-12-03 14:21:27 +00:00
Commented Dec 3, 2013 at 14:21 — Aziz Shaikh
– Aziz Shaikh, Commented Dec 3, 2013 at 14:21
If i remove set I can save procedure but rank is always 0

zajca
– zajca

2013-12-03 14:32:28 +00:00
Commented Dec 3, 2013 at 14:32 — zajca
– zajca, Commented Dec 3, 2013 at 14:32

wchiquito · Accepted Answer · 2013-12-03 14:42:17Z

3

You need to continue using a 9.4. User-Defined Variables, not a 13.6.4.1. Local Variable DECLARE Syntax:

BEGIN
    -- DECLARE variable INT DEFAULT 0;
    SELECT rank, best_time, user_id
    FROM (
        -- SELECT SET variable = variable + 1 AS rank, best_time, user_id
        SELECT @variable := @variable + 1 AS rank, best_time, user_id
        FROM database.user_round, (SELECT @variable := 0) init
        WHERE round_id = 1
        ORDER BY best_time ASC
    ) AS result
    WHERE user_id = 1;
END

answered Dec 3, 2013 at 14:42

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

3 Comments

Aziz Shaikh Over a year ago

In this case you may have to watch out for this:

The difference between a procedure variable and a session variable is that procedure variable is reinitialized to NULL each time the procedure is called, while the session variable is not

as mentioned at: stackoverflow.com/a/1010042

wchiquito Over a year ago

@AzizShaikh: That's right, so it initializes the variable @variable in each execution of the stored procedure. See (SELECT @variable := 0) init

Martin Asenov Over a year ago

why should we stick to user variables, since they would fail the query if the stored procedure is called simultaneously from two or more threads?

Collectives™ on Stack Overflow

Mysql procedure increment variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related