Write a transaction block in Store procedure?

To perform the ROLLBACK in MySQL Stored Procedure, we must have to declare exit handler in stored procedure. There are two types of handler we can have in MySQL Stored Procedure. sqlexception sqlwarning sqlexception will execute when there is any error occurs during the query execution and sqlwarning will execute when any warning occurs in MySQL Stored Procedure. Let’s see how we can have those block in Stored Procedure. SAMPLE GOES HERE DELIMITER $$ CREATE PROCEDURE `transaction_sp` () BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR ROLLBACK; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING ROLLBACK; END; START TRANSACTION; -- ADD option 5 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0); SET poid = (SELECT LAST_INSERT_ID()); INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+'); -- ADD option 12 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1); -- ADD option 13 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0); COMMIT; END $$

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Anjali, let me give one unasked for friendly advice. By just posting the interview questions here and reading their answer you are not going to gain much knowledge. Trust me.

Understanding concepts from beginning is very important for which study is inevitable. It is a time consuming process, but there is no going around it. Please refer to books like SQL Server Unleashed for thorough coverage of such topics.

Please try to take this in right spirit. I am telling this to your long term benefit.
Rathrola Prem Kumar9y ago
To perform the ROLLBACK in MySQL Stored Procedure, we must have to declare exit handler in stored procedure. There are two types of handler we can have in MySQL Stored Procedure. sqlexception sqlwarning sqlexception will execute when there is any error occurs during the query execution and sqlwarning will execute when any warning occurs in MySQL Stored Procedure. Let’s see how we can have those block in Stored Procedure. SAMPLE GOES HERE DELIMITER $$ CREATE PROCEDURE `transaction_sp` () BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR ROLLBACK; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING ROLLBACK; END; START TRANSACTION; -- ADD option 5 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0); SET poid = (SELECT LAST_INSERT_ID()); INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+'); -- ADD option 12 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1); -- ADD option 13 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0); COMMIT; END $$