Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

PetroGromovo's avatar

How to fix Setting user variables within expressions is deprecated errors in 2 procedures?

Ddebuging a mysql 8 stored procedure I call in dbeaver as :

call sp_taskComplete(@in_taskId := 1, @in_completeSubtasks := 0, @in_userId := 11, @returnCode);

and I got about 20 warnings :

Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

Code of procedures:

create procedure sp_taskComplete(IN in_taskId bigint unsigned,
                                                         IN in_completeSubtasks tinyint(1),
                                                         IN in_userId bigint unsigned,
                                                         OUT out_returnCode bigint unsigned)
BEGIN
    DECLARE taskCreatorId bigint unsigned;

    IF (in_userId != taskCreatorId) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Only creator can complete task !';
        SET out_returnCode := -1;
    END IF;

    CALL sp_checkCompleteSubtasks(@in_taskId := in_taskId, @in_completeSubtasks = in_completeSubtasks, @out_returnCode);

    UPDATE tasks SET status = 'D', completed_at = NOW() WHERE tasks.id = in_taskId;

    SET out_returnCode := in_taskId;
END;

and

create procedure sp_checkCompleteSubtasks(IN in_taskId bigint unsigned,
                                                                  IN in_completeSubtasks tinyint(1),
                                                                  OUT out_returnCode bigint unsigned)
BEGIN
    DECLARE childTaskId smallint unsigned;
    DECLARE childTaskTitle varchar(100);
    DECLARE childTaskStatus varchar(1);
    DECLARE done INT DEFAULT FALSE;
    DECLARE tasksCursor cursor
        for SELECT tasks.id, tasks.title, tasks.status
            FROM tasks
            WHERE tasks.parent_id = in_taskId
            ORDER BY tasks.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    open tasksCursor;
    childrenTasksLoop:
    loop
        -- get all children of in_taskId and check them
        fetch tasksCursor into childTaskId, childTaskTitle, childTaskStatus;
        IF(in_completeSubtasks and childTaskStatus != 'D') THEN
            UPDATE tasks SET status = 'D', completed_at = NOW() WHERE tasks.id = childTaskId;
        END IF;

        IF(!in_completeSubtasks and childTaskStatus = 'T') THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Can not complete the task, as it has uncompleted subtask with id : ';
        END IF;

        IF done THEN
            LEAVE childrenTasksLoop;
        END IF;

    end loop childrenTasksLoop;
    close tasksCursor;

    SET out_returnCode := in_taskId;
END;

Which lines of my code could raise this error and how to debug it ?

Thanks in advance!

0 likes
1 reply
PetroGromovo's avatar
PetroGromovo
OP
Best Answer
Level 5

I removed named parameters with @ :

    CALL sp_checkCompleteSubtasks(in_taskId, in_completeSubtasks, out_returnCode);

and that fixed the problem

Please or to participate in this conversation.