Hello. Faced a task and ask for help to solve it. So, it is necessary to delete the data about the employee, at the same time it is necessary to check whether there are no planned procedures for this employee, and if there are outstanding procedures, it is necessary to return its cost to the client’s account. I've been throwing something over here, but maybe there are some more acceptable options, because This request is looping.

CREATE PROCEDURE `RemovePersonnel`( -- Add the parameters for the stored procedure here id CHAR(36) ) BEGIN CREATE TEMPORARY TABLE temp AS (SELECT * FROM clientprocedures WHERE clientprocedures.IdPersonnel = id); WHILE EXISTS (SELECT Id FROM temp) DO SELECT @CostOldClientProcedure := temp.Cost, @IdOldClient := temp.IdClient, @DateClientProcedure := temp.Date FROM temp WHERE temp.Id = Id; IF (@DateClientProcedure > now()) THEN UPDATE client SET Balance = Balance + @CostOldClientProcedure WHERE client.Id = @IdOldClient; END IF; IF @@error_count = 0 THEN commit; ELSE rollback; END IF; DELETE FROM temp WHERE temp.Id = Id; END WHILE; DELETE FROM personnel WHERE personnel.Id = id; END

    1 answer 1

    1. Never name the function parameters in the same way as at least one column in at least one table used in the procedure. For the compiler will not be able to distinguish them. Apparently in you in clientprocedures there is an ID column and your first query is interpreted by the compiler like this:

       SELECT * FROM clientprocedures WHERE clientprocedures.IdPersonnel = clientprocedures.id

      What records he chooses so difficult to predict.

    2. You have an error in the select ... from temp WHERE temp.Id = Id line select ... from temp WHERE temp.Id = Id , firstly there is the same problem as in item 1, secondly you have id in the variable, even if it was used, still contains the parameter passed to function and it is very likely that select does not return a single row (hence the looping).

    3. SQL is a powerful language. It is necessary to use the cursors sequentially sorting the records in the table only in very exotic situations. Almost any problem can be solved by a single request. Your entire cycle is perfectly replaced by a single query, and without using any temporary tables:

       update client C inner join( select IdClient, sum(Cost) as Cost from clientprocedures where IdPersonnel = NNN and Date > now() group by IdClient ) B on C.id=B.IdClient set C.Balance=C.Balance+B.Cost