mysql stored procedure print error message and rollback -

mysql stored procedure print error message and rollback -

i trying write stored procedure print error message first , rollback

i tried dose not work

i able rollback in case of error dose not print error message

delimiter create procedure transaction_sp () begin declare exit handler sqlexception begin -- error -------------------------------------------------------------------------------------- select "error message '%s' , errorno '%d'"------- part in not working -------------------------------------------------------------------------------------- rollback; end; declare exit handler sqlwarning begin -- warning -------------------------------------------------------------------------------------- select "warning message '%s' , errorno '%d'"------- part in not working -------------------------------------------------------------------------------------- rollback; end; start transaction; -- add together alternative 5 insert product_option(product_id,option_id,required) values(insertedproductid,5,0); set poid = (select last_insert_id()); insert 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 together alternative 12 insert product_option(product_id,option_id,required) values(insertedproductid,12,1); -- add together alternative 13 insert product_option(product_id,option_id,required) values(insertedproductid,13,0); commit; end $$

so how can accomplish stored procedure

this worked me

delimiter create procedure transaction_sp () begin declare exit handler sqlexception begin -- error -------------------------------------------------------------------------------------- --select "error message '%s' , errorno '%d'"------- part in not working -------------------------------------------------------------------------------------- diagnostics status 1 @p1 = returned_sqlstate, @p2 = message_text; select @p1 returned_sqlstate , @p2 message_text; rollback; end; declare exit handler sqlwarning begin -- warning -------------------------------------------------------------------------------------- -- select "warning message '%s' , errorno '%d'"------- part in not working -------------------------------------------------------------------------------------- diagnostics status 1 @p1 = returned_sqlstate, @p2 = message_text; select @p1 returned_sqlstate , @p2 message_text; rollback; end; start transaction; -- add together alternative 5 insert product_option(product_id,option_id,required) values(insertedproductid,5,0); set poid = (select last_insert_id()); insert 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 together alternative 12 insert product_option(product_id,option_id,required) values(insertedproductid,12,1); -- add together alternative 13 insert product_option(product_id,option_id,required) values(insertedproductid,13,0); commit; end $$

mysql stored-procedures