Cannot declare a variable in a stored procedure

Question

I'm unable to declare a mysql variable inside a stored procedure. Also i cannot open a cursor as well i keep getting the same error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@userID INT; DECLARE @cursorUserID CURSOR FOR SELECT (MAX(userID) + 1) FROM Use' at line 3.

Here's the code:

use phpBank;
DELIMITER //
CREATE PROCEDURE newUser()
BEGIN
       DECLARE @userID INT;
       DECLARE @cursorUserID CURSOR FOR SELECT (MAX(userID) + 1) FROM Users;
       OPEN @newUserID;
END;
//
DELIMITER ;

Why would you possibly need a cursor to just select one row..? — peterm
– peterm, Commented Jan 7, 2014 at 8:23
Using this code i get the error: 'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@userID int; DECLARE @cursorUserID AS CURSOR; — user3168167
– user3168167, Commented Jan 9, 2014 at 3:32

peterm · Accepted Answer · 2014-01-09 03:57:47Z

As it has already been mentioned by others you're mixing local variables (without @ in front of a variable name) and user(session) variables (that have @ in front of a variable name). DECLARE is used only for local variables. Session variables are always available and don't require declaration.
It a good practice not to give your variables and procedure parameters the same names as column names in your tables. It can easily blow your code in situation when MySQL can't decide what you actually meant to use a variable or a column.
You definitely don't need a cursor (unlike plsql in Oracle) to return a scalar value by using an aggregate function (MAX() in your case). Just put a select in parentheses.

That being said a working syntactically correct version of your code might look like this

DELIMITER $$
CREATE PROCEDURE newUser()
BEGIN
    DECLARE newid INT;

    SET newid = (SELECT COALESCE(MAX(userID), 0) + 1 FROM Users);
    SELECT newid;
END$$
DELIMITER ;

or technically you can also can do (but not recommended) this

DELIMITER $$
CREATE PROCEDURE newUser()
BEGIN
    SET @newid = (SELECT COALESCE(MAX(userID), 0) + 1 FROM Users);
    SELECT @newid newid;
END$$
DELIMITER ;

Here is SQLFiddle demo

Now, deducing from procedure name and a query you're trying to use it look like you're reinventing a weel of generating unique ids (most likely PK) doing it manually. Stop! Don't do that. It creates racing conditions and won't work correctly in concurrent environment, because multiple processes can potentially grab the same MAX(userID) value.

The only safe method for generating unique ids in MySQL is auto_increment column. Use it instead.

zzlalani · Accepted Answer · 2014-01-07 08:11:18Z

0

Try this

use phpBank;

DELIMITER //
CREATE PROCEDURE newUser()

BEGIN
        DECLARE @userID int;
        DECLARE @cursorUserID AS CURSOR; 
        SET @cursorUserID = CURSOR FOR SELECT (MAX(userID) + 1) FROM Users;

        OPEN @newUserID;

END;
//
DELIMITER ;

answered Jan 7, 2014 at 8:11

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

Comments

M Khalid Junaid · Accepted Answer · 2014-01-07 08:13:50Z

0

Try this

DELIMITER //
    CREATE PROCEDURE newUser()
    BEGIN 
            DECLARE userID INT;
            DECLARE cursorUserID CURSOR FOR SELECT (MAX(userID) + 1) FROM Users;
            -- OPEN newUserID;

    END;
    //
    DELIMITER ;

CALL newUser();

answered Jan 7, 2014 at 8:13

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

1 Comment

user3168167 Over a year ago

Using this code i get the error: 'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@userID INT; DECLARE @cursorUserID CURSOR FOR SELECT (MAX(userID) + 1) FROM Use' at line 3'

Collectives™ on Stack Overflow

Cannot declare a variable in a stored procedure

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related