Level 5
I removed named parameters with @ :
CALL sp_checkCompleteSubtasks(in_taskId, in_completeSubtasks, out_returnCode);
and that fixed the problem
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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!
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.