-- =============================================

-- GetAccounts Procedure

-- Retrieves records from the Account table

-- =============================================

CREATE PROCEDURE GetAccounts

AS

SELECT AccountID, CustomerName, Balance

FROM Account

-- =============================================

-- TransferFunds Procedure

-- Uses a Transanction to updates two records

-- in the Account table to reflect transferring
-- funds from one account to another account.

-- The Transanction allows RollBack to undo
-- changes to records in the database when an

-- error occurs.

-- =============================================

CREATE PROCEDURE TransferFunds

@ToAccount int,

@FromAccount int,

@Amount float

AS

BEGIN TRANSACTION

BEGIN

UPDATE Account
SET Balance = Balance - @Amount
WHERE AccountID = @FromAccount

IF @@RowCount = 0 GOTO on_Error

UPDATE Account
SET Balance = Balance + @Amount
WHERE AccountID = @ToAccount

IF @@RowCount = 0 GOTO on_Error

COMMIT TRANSACTION

END

RETURN

on_Error:

ROLLBACK TRANSACTION

RETURN