mysql-transactions-in-store-proc.sql

#Con IF

DELIMITER $$ 
CREATE PROCEDURE `sp_fail`() 
BEGIN 
  DECLARE `_rollback` BOOL DEFAULT 0; 
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1; 
  START TRANSACTION; 
    INSERT INTO `tablea` (`date`) VALUES (NOW()); 
    INSERT INTO `tableb` (`date`) VALUES (NOW()); 
    INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL 
  IF `_rollback` THEN 
    ROLLBACK; 
  ELSE 
    COMMIT; 
  END IF; 
END$$ 
DELIMITER ;

================================================


CREATE PROCEDURE `sp_fail`() 
BEGIN
  DECLARE `_rollback` BOOL DEFAULT 0;                                          
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN 
   SET `_rollback` = 1; -- rollback any changes made in the transaction 
    RESIGNAL; -- raise again the sql exception to the caller 
  END; 
  START TRANSACTION; 
    insert into myTable values1 ... 
  IF `_rollback` THEN 
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error detected.', MYSQL_ERRNO = 2000; 
  END IF; 
    insert into myTable values2 ... 
    -- this will not be executed 
    COMMIT; -- this will not be executed
END$$

=========================================================================
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CREATE_SERVER_USER`(
  IN P_server_id VARCHAR(100), 
  IN P_db_user_pw_creds VARCHAR(32), 
  IN p_premium_status_name VARCHAR(100), 
  IN P_premium_status_limit INT, 
  IN P_user_tag VARCHAR(255), 
  IN P_first_name VARCHAR(50), 
  IN P_last_name VARCHAR(50) ) 
                                          
  BEGIN DECLARE errno INT; 
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO; 
  SELECT errno AS MYSQL_ERROR; 
   ROLLBACK; 
  END; 
  START TRANSACTION;
      INSERT INTO server_users(server_id, db_user_pw_creds, premium_status_name, premium_status_limit) 
                        VALUES(P_server_id, P_db_user_pw_creds, P_premium_status_name, P_premium_status_limit); 
      INSERT INTO client_users(user_id, server_id, user_tag, first_name, last_name, lat, lng) 
                        VALUES(P_server_id, P_server_id, P_user_tag, P_first_name, P_last_name, 0, 0); 
   COMMIT WORK; 
  END$$ 
DELIMITER ;