CODEWITHSUNDEEP
mysqlsqlstored-procedures
user3168167
user3168167

Reputation: 23

Cannot declare a variable in a stored procedure

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 ;

Upvotes: 1

Views: 7018

Answers (3)

peterm
peterm

Reputation: 92785

  1. 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.

  2. 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.

  3. 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.

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 0

zzlalani
zzlalani

Reputation: 24364

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 ;

Upvotes: 0

Related Questions