Create a stored procedure called "sp_TransferAccounts"

The stored procedure will take three IN parameters:

• Source account number
• Destination account number
• Transfer amount

The stored procedure will take on OUT parameter called 'transaction_status'

The stored procedure will contain a transaction with rollback.  The transaction must include the following:

• Check if the source account is active (i.e. status = 'open')
• Check if the destination account is active
• Check if the source account has sufficient funds

If both accounts are active and the source account has sufficient funds:

• Update the source account balance (subtract the transfer amount)
• Update the destination account balance (add transfer amount)
• Need an entry in the transactions table
• Set the OUT parameter called 'transaction_status' to 'success'

If either a, b, or c conditions are not met (i.e. one of the accounts is not active, or insufficient funds), set the OUT parameter called 'transaction_status' to an appropriate error message

If a transaction is successful, commit

If a transaction is not successful, rollback

Answer rating: 100% (QA)

Heres a stored procedure named spTransferAccounts that fulfills the requirements you mentioned DELIMITER CREATE PROCEDURE spTransferAccounts IN source... View the full answer