I need help creating a procedure

Please I need some body to guide me here. I dont know how to create a store procedure.

If the user wants to delete one transaction I need to do the following to be able to delete the transaction. What I'm trying to do here is update the items to the state that they were before the transaction.

What I want to do is something like this, I never have made a store procedure this is just an example to make easy to understand what I need to do.

CREATE PROCEDURE `videodb`.`CancelTransaction` (transaction_id INT)
BEGIN

DECLARE sale_types VARCHAR(25);
DECLARE sale_ids INT;
DECLARE item_ids INT;
DECLARE deposit_ids INT;

/*Here I need to select the required fields that are needed to do what’s next*/

SELECT rents.id, rents.item_id, rents.type, deposit_id INTO sale_ids, item_ids, sale_types, deposit_ids
FROM VideoDB.transactions
INNER JOIN VideoDB.rents ON rents.transaction_id=transaction.id
WHERE id=transaction_id;

WHILE (sale_types <> NULL) DO

SELECT CASE WHEN (sale_types='Rent') THEN

/*Here the rent is deleted and the item is updated to available*/

DELETE FROM VideoDB.rents WHERE id=sale_ids;

UPDATE VideoDB.TransactionItems SET status='Available' WHERE id=item_ids;

WHEN (sale_types='Sale') Then

/*Here the sale is deleted and if the item that was sold was a sale item the inventory is updated and if the item that was sold was a rent item the item is updated to available*/

DECLARE cur_keep_inventory BIT;
DECLARE cur_item_type VARCHAR(25);
DECLARE cur_item_inventory INT;

DELETE FROM VideoDB.rents WHERE id=sale_ids;

SELECT keep_inventory, inventory, item_type INTO cur_keep_inventory, cur_item_inventory, cur_item_type
FROM VideoDB.items WHERE id=item_ids;

SELECT CASE WHEN (cur_keep_inventory=1 And cur_item_type='Sale') THEN

UPDATE VideoDB.items SET quantity=cur_item_inventory+1 WHERE id=item_ids;

WHEN (cur_item_type='Rent') THEN

UPDATE VideoDB.items SET status='Avalable' WHERE id=item_ids;

END;

WHEN (sale_types='Overdue Payment') Then

/*Here the Overdue deposit is added to the current overdue and is deleted*/

DECLARE cur_paid_amount DOUBLE;
DECLARE cur_deposit_id INT;
DECLARE cur_overdue_amount DOUBLE;

SELECT paid_amount, deposit_id, r.paid_amount INTO cur_paid_amount, cur_deposit_id, cur_overdue_amount
FROM VideoDB.rents, VideoDB.rents r WHERE rents.deposit_id=r.id AND id=sale_ids;

UPDATE VideoDB.rents SET paid_amount=cur_overdue_amount+cur_paid_amount WHERE id=cur_deposit_id;

DELETE FROM VideoDB.rents WHERE id=sale_ids;

WHEN (sale_types='Debt Payment') Then

/*Here the Debt deposit is added to the current debt and is deleted*/

DECLARE cur_paid_amount DOUBLE;
DECLARE cur_deposit_id INT;
DECLARE cur_debt_amount DOUBLE;

SELECT paid_amount, deposit_id, r.paid_amount INTO cur_paid_amount, cur_deposit_id, cur_overdue_amount
FROM VideoDB.rents, VideoDB.rents r WHERE rents.deposit_id=r.id AND id=sale_ids;

UPDATE VideoDB.rents SET paid_amount=cur_debt_amount+cur_paid_amount WHERE id=cur_deposit_id;

DELETE FROM VideoDB.rents WHERE id=sale_ids;

END;
END WHILE;
END

Amendez:

Your pseudocode looks like you have the problem pretty well thought through. The next step for you is to convert your pseudocode into actual SQL syntax. Use books online and begin the conversion. Since you are new to this my feeling is that you need to do this learning phase on your own as much as possible so I will leave the bulk of this work to you. Some topics that you are going to need to examine are (1) variables, (2) cursors -- I don't like cursors, but that appears to be the way your pseudocode is aimed, (3) IF and ELSE statements, (4) WHILE, (5) CREATE PROCEDURE and more. Make a full pass at converting your pseudocode into a stored procedure and then let us know how it is going.

Something that you are going to need to learn somewhere down the road are reasons to avoid using cursors. The main thing that you need to know about this for the moment is that MS SQL Server is more efficient when operating on sets of records than when working on records one at a time. Think about this for now and we will discuss this after you have completed your first pass.

Good Luck,
Dave