Unable to determine syntax problem with creation of stored procedure in MySQL-CodePudding

I'm trying to create the following stored procedure (XAMPP > phpMyAdmin):

        DROP PROCEDURE IF EXISTS changeTurn;
        
        DELIMITER //
        
        create procedure changeTurn(in currentGameID bigint(20))
        begin
            declare turnHasBeenChanged tinyint(1) default 0
            
            select @targetPlayer := player_number from game_players where gameID = currentGameID and current_turn = 1

            set @targetPlayer = @targetPlayer   1

            update game_players set current_turn = 0 where gameID = currentGameID and current_turn = 1

            while turnHasBeenChanged = 0 do
                case
                    when @targetPlayer > 4
                    then set @targetPlayer = 1
                end
                case
                    when (select forfeit_next_turn from game_players where gameID = currentGameID and player_number = @targetPlayer) = 1
                    then
                    update game_players set forfeit_next_turn = 0 where gameID = currentGameID and player_number = @targetPlayer
                    set @targetPlayer = @targetPlayer   1
                    else
                    update game_players set current_turn = 1 where gameID = currentGameID and player_number = @targetPlayer
                    set turnHasBeenChanged = 1
                end
            end while
        end
        
        end //
        DELIMITER ;

I get the following issue with or without the delimiter:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select @targetPlayer := player_number from game_players where gameID = curren...' at line 5

Unfortunately, this is not very specific.

When piecing this procedure together, I made sure I was using the correct syntax for each piece. But now it's altogether, it's borked. Please can you give me some direction as to why this is going wrong?

I checked line 4 (declare turnHasBeenChanged tinyint(1) default 0) in case its syntax was causing the succeeding line to fail, and added a semi-colon to the end of it, and that broke it at that line, so it wasn't that.

I then verified the syntax of line 5 against https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch01s15.html and it seems to be correct...?

CodePudding user response：

every comand has to ended by a ;

Also case when should be reaplce by an IF THEN

And at the end you have a end to much

so following gives no error any more

    DROP PROCEDURE IF EXISTS changeTurn;
    
    DELIMITER //
    
    create procedure changeTurn(in currentGameID bigint(20))
    begin
        declare turnHasBeenChanged tinyint(1) default 0;
        
        select @targetPlayer := player_number from game_players where gameID = currentGameID and current_turn = 1;

        set @targetPlayer = @targetPlayer   1;

        update game_players set current_turn = 0 where gameID = currentGameID and current_turn = 1;

        while turnHasBeenChanged = 0 do
            IF @targetPlayer > 4
                then set @targetPlayer = 1;
            end IF;
            IF ((select forfeit_next_turn from game_players where gameID = currentGameID and player_number = @targetPlayer) = 1)
                then
                update game_players set forfeit_next_turn = 0 where gameID = currentGameID and player_number = @targetPlayer;
                set @targetPlayer = @targetPlayer   1;
                else
                update game_players set current_turn = 1 where gameID = currentGameID and player_number = @targetPlayer;
                set turnHasBeenChanged = 1;
            end IF;
        end while;
    
    end //
    DELIMITER ;