MySQL: Run a stored procedure getting parameter from a query inside another stored procedure

Question

I am new to MySQL.

I am developing a system where many users are assigned to specific tasks. When they are inactive for a certain period of time (lets say more than 10 minutes) I would like the system automatically clear their assignments so that others can work on them.

To achieve that I have created a table called tblactivitytracker for activity tracking. Assignments are in a table called tblinquiries. I have created a stored procedure to get the inactive users.

Here is an sqlfiddle example: Get Inactive Users

In the above example I get 3 inactive users: auditor1, auditor2 and auditor3.

I have created a stored procedure to clear assignment of a single user which does the job perfectly.

CREATE PROCEDURE `spClearAssignedInquiry`(IN `pAssignedTo` VARCHAR(50))
UPDATE
        tblinquiries
SET
        AuditStatus='Check', AssignedTo=NULL, Result=NULL,
        ResultCategories=NULL, AuditBy=NULL,
        Remarks=NULL, StartTime=NULL, EndTime=NULL
WHERE
        AssignedTo=pAssignedTo AND
        AuditStatus='Assigned' AND EndTime IS NULL

If I pass auditor1 as a parameter in the above procedure it will clear the user's assignment.

To pass all inactive users and clear the assignments in a single go I tried the below procedure following this stackoverflow solution:

CREATE PROCEDURE `spInactiveUsers`()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE AssignedTo VARCHAR(50);
  DECLARE cur CURSOR FOR
    SELECT
    q1.AssignedTo  AS AssignedTo
FROM 
    (SELECT
        InquiryId, AssignedTo
    FROM
        tblinquiries
    WHERE
        AuditStatus='Assigned' AND StartTime IS NOT NULL AND EndTime IS NULL
    ORDER BY
        AssignedTo ASC
    ) q1
RIGHT JOIN
    (SELECT
        UserId, MAX(LastActivity) AS LastActivity, ROUND(TIME_TO_SEC(TIMEDIFF(MAX(LastActivity),CURRENT_TIMESTAMP()))/60,0) AS InactiveMinutes
    FROM 
        tblactivitytracker
    GROUP BY
        UserId
    ORDER BY
        LastActivity ASC
    ) q2
ON
    q2.UserId=q1.AssignedTo
WHERE
    q2.InactiveMinutes>10;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  testLoop: LOOP
    FETCH cur INTO AssignedTo;
    IF done THEN
      LEAVE testLoop;
    END IF;
    CALL spClearAssignedInquiry(AssignedTo);
  END LOOP testLoop;

  CLOSE cur;
END

But it does not clear any of the assignments.

I am banging my head to the wall for the last couple of days. Any help would be much appreciated. Thanks in advance.

Separately the queries run without any problem. Here is the data in fiddle: http://sqlfiddle.com/#!9/a6e573/2 — Lone Rider, Jul 15 '18 at 08:44

score 1 · Accepted Answer · answered Jul 15 '18 at 09:56

You are using a variable name that is also the name of a column. The value of the variable will take precedence over the column value, see the documentation:

A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.

So in

...
FROM 
 (SELECT
     InquiryId, AssignedTo
...

you are selecting the variable AssignedTo (which is null), not the column from your table.

Just rename it (in declare and the loop), or, less advised, explicitly state the tablename to set the scope, e.g. SELECT InquiryId, tblinquiries.AssignedTo .... order by tblinquiries.AssignedTo.

There is another (minor) problem is your use of TIMEDIFF in TIMEDIFF(MAX(LastActivity), CURRENT_TIMESTAMP()). It requires the later time in the first argument if you want to get a positive number (as in q2.InactiveMinutes>10).

Thank you very much! I renamed the variable and that did the trick. — Lone Rider, Jul 15 '18 at 12:58

MySQL: Run a stored procedure getting parameter from a query inside another stored procedure

1 Answers1