How to use transaction and stored procedure at the same time?

Question

Create a stored procedure that returns the total number of copies of a book our library owns, given a title A. First,find the standard number(S) of A. Then, go through the ITEM table to find tuples where standard Number= S. Then, add the number of copies together to find the total number of copies the whole library system owns. Could please teach me how to fix these code?

use library;

select * from item;
DELIMITER $$
DROP PROCEDURE IF EXISTS FindNumberOfCopies;

CREATE PROCEDURE FindNumberOfCopies ( IN itemTitle varchar(150))
BEGIN
SELECT copies FROM item WHERE title = itemTitle;
ROLLBACK;
END;
START TRANSACTION;
    UPDATE item SET copies = newCopies where title = itemTitle;
COMMIT;
END
$$

Answer 1

DROP PROCEDURE IF EXISTS FindNumberOfCopies;
DELIMITER $$
CREATE PROCEDURE FindNumberOfCopies
( 
 IN itemTitle varchar(150)
)
BEGIN
    SELECT copies FROM item WHERE title = itemTitle;

    UPDATE item SET copies = newCopies where title = itemTitle;
END
$$
DELIMITER ;

There is no point in your select. There is no point in your transaction. You did a rollback of a transaction that did not even occur.

Each sql call regardless is a transaction. You are wrapping a trans with a trans. Along the way of this edit I somehow removed the 1064