Error 1604 Creating MySQL Stored Procedure. MySQL Version 5.7.19

Question

I am trying to translate a SQL stored procedure I have written in the past to MySQL. This error is giving me trouble.

I am using phpmyadmin 4.7.4 to create this procedure The error I am getting is at SET userID = SELECT MAX(ID) + 1 FROM users I have also placed a tag before it in the code so it is easier for you guys to find.

The error that is outputted is:

MySQL said: Documentation /#1064 - 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 'SET userID = SELECT MAX(ID) + 1 FROM users; -- Default to 1 if the table is em' at line 13

CREATE PROCEDURE uspAddUser(username VARCHAR(50), email VARCHAR(50), password VARCHAR(50), avatar VARCHAR(50))
BEGIN
DECLARE userID   INTEGER;
BEGIN
ROLLBACK;   -- Rollback transaction on error
END;
START TRANSACTION
-- Get the next highest ID and lock the table until the end of the transaction

<ERROR> -> SET userID = SELECT MAX(ID) + 1 FROM users;
-- Default to 1 if the table is empty

SET userID = COALESCE(userID, 1);
-- CREATE new record

INSERT INTO users(userID, username, email, password, avatar)
VALUES(ID, email, password, avatar, 1);  -- 1 = Active

-- return ID to calling program
SELECT userID AS ID;

COMMIT;
END;//

This is the original SQL query if you guys want to see that at all

GO
CREATE PROCEDURE uspAddTeam
 @strTeam           VARCHAR(50)
,@strMascot         VARCHAR(50)

AS
SET NOCOUNT ON      -- Report Only Errors
SET XACT_ABORT ON   -- Rollback transaction on error

BEGIN TRANSACTION

DECLARE @intTeamID   INTEGER

-- Get the next highest ID and lock the table until the end of the transaction
SELECT @intTeamID = MAX(intTeamID) + 1 FROM TTeams (TABLOCKX)

-- Default to 1 if the table is empty
SELECT @intTeamID = COALESCE(@intTeamID, 1)

-- CREATE new record
INSERT INTO TTeams(intTeamID, strTeam, strMascot, intTeamStatusID)
VALUES(@intTeamID, @strTeam, @strMascot, 1)  -- 1 = Active

-- return ID to calling program
SELECT @intTeamID AS intTeamID

COMMIT TRANSACTION
GO

Zymon Castaneda · Accepted Answer · 2018-03-23 02:44:12Z

1

You can try this:

DROP PROCEDURE IF EXISTS `uspAddUser`;
DELIMITER //
CREATE PROCEDURE `uspAddUser` (username VARCHAR(50), email VARCHAR(50), password VARCHAR(50), avatar VARCHAR(50))
BEGIN
    DECLARE `userID` BIGINT(20);  

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;        
    END;    

    START TRANSACTION;
    -- Get the next highest ID and lock the table until the end of the transaction
    SET userID = (SELECT MAX(ID) + 1 FROM users);
    -- Default to 1 if the table is empty
    SET userID = COALESCE(userID, 1);
    -- CREATE new record
    INSERT INTO users(userID, username, email, password, avatar)
    VALUES(ID, email, password, avatar, 1);  -- 1 = Active
    -- return ID to calling program
    SELECT userID AS ID;
    COMMIT;
END//
DELIMITER ;

Question(s)

Does the table with ID is set as an auto-increment column? If not, please do.. because it will eliminate the part:

-- Get the next highest ID and lock the table until the end of the transaction
SET userID = (SELECT MAX(ID) + 1 FROM users);

edited Mar 23, 2018 at 2:44

answered Mar 23, 2018 at 1:07

Zymon Castaneda

75912 silver badges23 bronze badges

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

3 Comments

Adam Schneider Over a year ago

you are absolutely right, I was just copy and pasting the stored procedure in but yeah I totally don't need the coalesce line.

Adam Schneider Over a year ago

I ran your query and got this error. #1337 - Variable or condition declaration after cursor or handler declaration. the error quotes all the code above including this line -> -- Get the next highest ID and lock the table until the end of the transaction

Adam Schneider Over a year ago

I just tried moving this line DECLARE userID INTEGER; to below start transaction, nothing.

Collectives™ on Stack Overflow

Error 1604 Creating MySQL Stored Procedure. MySQL Version 5.7.19

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related