CODEWITHSUNDEEP
mysqldatabase
Ahmed Hosny
Ahmed Hosny

Reputation: 480

How to insert multiple records in one stored procedure and rollback if one fails - MySQL

I have a stored procedure that createAssessments and createAssessmentMarks for students of that particular section.

Whenever I insert single records it executed successfully but when I try to insert multiple records at one as "Subquery returns more than one-row" error and stores data in assesmentsTable , but not the AssesmentMarksTable

So my questions are:

  1. How can I insert multiple records at one execution?
  2. How can I rollback if something fails to prevent inconsistent data storage?
       DELIMITER $$
    
        CREATE PROCEDURE createAssessment(IN name VARCHAR(100), maxMarks INT, classId INT, sectionId INT , subjectId INT)
    
        BEGIN
    
        INSERT INTO assessments (name , maxMarks , classId , sectionId , subjectId) VALUES (name , maxMarks, classId , sectionId , subjectId);
        
        INSERT INTO assessmentMarks  (assesmentId , scoredMarks , studentId) VALUES (SELECT LAST_INSERT_ID() FROM assessments) , (scoredMarks) ,(SELECT stud.studentId FROM students stud WHERE stud.sectionId = sectionId); 
    
     
        END $$
    
        DELIMITER ;

Upvotes: 3

Views: 519

Answers (1)

Ahmed Hosny
Ahmed Hosny

Reputation: 480

Thanks @tadman for the hint

After some research I found these as an answer to above two questions:-

DELIMITER $$
CREATE PROCEDURE createAssesment(IN name VARCHAR(100), maxMarks INT, classId INT, sectionId INT , subjectId INT)
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;  -- rollback any changes made in the transaction
    END;
SET autocommit = 0;
START TRANSACTION ;
INSERT INTO assesments (name , maxMarks , classId , sectionId , subjectId) VALUES (name , maxMarks , classId , sectionId , subjectId);
INSERT INTO assesmentMarks  (assesmentId  , studentId )
SELECT LAST_INSERT_ID() , stud.studentId  FROM assesments a CROSS JOIN Students stud WHERE stud.sectionId = sectionId;
COMMIT ;
SET autocommit = 1;
END $$
DELIMITER ;

Upvotes: 2

Related Questions