mysql stored procedure print error message and rollback

Question

i am trying to write a stored procedure that will print the error message first and then rollback

i tried this but this dose not work

i am able to rollback it but in case of error it dose not print the error message

DELIMITER 

CREATE PROCEDURE transaction_sp ()

BEGIN

DECLARE exit handler for sqlexception
BEGIN
-- ERROR
-------------------------------------------------------------------------------------- 
select "error message '%s' and errorno '%d'"------- this part in not working
-------------------------------------------------------------------------------------- 
ROLLBACK;
END;

DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
-------------------------------------------------------------------------------------- 
 select "warning message '%s' and errorno '%d'"------- this part in not working
-------------------------------------------------------------------------------------- 
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,pr ice_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
$$

so how can i achieve it with this stored procedure

Answer 1

this worked for me

DELIMITER 

CREATE PROCEDURE transaction_sp ()

BEGIN

DECLARE exit handler for sqlexception
BEGIN
-- ERROR
-------------------------------------------------------------------------------------- 
--select "error message '%s' and errorno '%d'"------- this part in not working
-------------------------------------------------------------------------------------- 
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 as RETURNED_SQLSTATE  , @p2 as MESSAGE_TEXT;
ROLLBACK;
END;

DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
-------------------------------------------------------------------------------------- 
-- select "warning message '%s' and errorno '%d'"------- this part in not working
-------------------------------------------------------------------------------------- 
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 as RETURNED_SQLSTATE  , @p2 as MESSAGE_TEXT;
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,pr ice_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
$$