title: MySQL Catching Exceptions in Transactions
body: <p>What is the best way to return the cause of an error within a transaction in mysql stored procedure? </p>
  
  <p>Here's one stored procedure I created. But I noticed that if I commit an error on the syntax, say for instance I forget to put the <code>WHERE</code> clause in one of the <code>update</code> statements, it would only return the <code>SELECT 'error'</code> but not the actual cause why the error occurred.</p>
  
  <p>Is there any better way to catch errors just like in Java where you just put</p>
  
  <pre><code>catch(SQLException e){
  JOptionPane.showMessageDialog(null,e.getError()+"\n"+e.getMessage() );
  }
  </code></pre>
  
  <p>I'd appreciate any example or best practices. </p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `updateCurriculumDetails`(p_curriculumId INT, p_editedByUserId INT, p_curriculumName varchar(50), p_description VARCHAR(200), p_gradeLevelId INT, p_schoolYearId INT)
  BEGIN
  
      DECLARE hasError BOOLEAN DEFAULT 0;
      DECLARE CONTINUE HANDLER FOR sqlexception SET hasError = 1;
  
  
      START TRANSACTION;
  
          UPDATE curriculum_dt c
              SET     c.`name`        = p_curriculumName, 
                      c.description   = p_description,
                      c.gradeLevelId  = p_gradeLevelId 
              WHERE   c.id            = p_curriculumId;
  
          UPDATE curriculum_schoolyears_dt cs
              SET     cs.schoolYearId = p_schoolYearId
              WHERE   cs.curriculumId = p_curriculumId;
  
          UPDATE curriculum_hd SET editedBy_UserId = p_editedByUserId,
          dateLastEdited = NOW()
              WHERE curriculumId = p_curriculumId;
  
  
      IF hasError THEN
          ROLLBACK;
          SELECT 'error';
      ELSE
          COMMIT;
          SELECT 'successfully updated' AS result;
  
      END IF;
  
  END
  </code></pre>
  
  <p>Thanks in advance. </p>