MySQL Forums
Forum List  »  Stored Procedures

Need help in exception handling with transaction in SP
Posted by: beril sing
Date: June 10, 2017 06:44PM

Hi All,

I am new to MySql. I am a MSSQL developer. I am really very comfortable in MSSQL but it is really hard for me to write SP and handle transaction rollback exception handling. And I dont see any good example for this in official site.

DELIMITER $$

CREATE
PROCEDURE `SP_BNCJD`(IN TYPE_ID INT)

BEGIN

DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE result TEXT;
DECLARE ERRNO TEXT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT, ERRNO = MYSQL_ERRNO;

SHOW ERRORS;
ROLLBACK;
SET result = CONCAT('[SQLSTATE : ',code,'] [MYSQL_ERRNO : ',ERRNO ,'] MESSAGE : ',msg,'At SCENERIO_TYPE_ID :',TYPE_ID);
UPDATE EXCEPTION_LOG
SET EXCEPTION_LOG_DESC = result
WHERE EXCEPTION_LOG_DESC = 'SSIS PACKAGE STARTED'
AND SERVICE_REQUEST_NAME = 'SSIS_NONBLUEDOOR_TO_BLUEDOOR_MIG'
AND DATE(CREATED_TSTAMP) = DATE(CURRENT_TIMESTAMP());

END;
START TRANSACTION;


IF(SCENERIO_TYPE_ID = 1) THEN
BEGIN
BLAH BLAH
END
END$$
DELIMITER ;

I wanted to update the error message to EXCEPTION_LOG table and then rollback the transaction if any error occurs in the above sp else to commit and update success log to the same log table. but i really dont know how to do it ... i will execute the above sp via MSSQL SSIS Package through ODBC driver . Please help me out

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help in exception handling with transaction in SP
6268
June 10, 2017 06:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.