CREATE PROCEDURE [dbo].[performTransaction]

(

  @FromAccountID INT,

  @ToAccountID INT, 

  @Amount FLOAT

)

AS

BEGIN

-- Check to see if accounts exist

DECLARE @FromAccountTest INT

SELECT @FromAccountTest = (SELECT Count(*) 

  FROM Accounts

  WHERE AccountID = @FromAccountID)

IF @FromAccountTest <> 1 

BEGIN

 RAISERROR('From Account Does Not Exist', 10, 1)

END

DECLARE @ToAccountTest INT

SELECT @ToAccountTest = (SELECT Count(*) 

  FROM Accounts

  WHERE AccountID = @ToAccountID)

IF @FromAccountTest <> 1 

BEGIN

 RAISERROR('To Account Does Not Exist', 10, 2)

END

-- At this point we can log the transaction

DECLARE @TransactionID INT 

EXEC @TransactionID = LogTransactionAttempt @FromAccountID, @ToAccountID, @Amount 

-- Check to see if sufficient balance

DECLARE @FromAccountBalance  FLOAT

SELECT @FromAccountBalance = (SELECT Balance

    FROM Accounts 

    WHERE AccountID = @FromAccountID)

IF (@FromAccountBalance < @Amount)     

BEGIN

  RAISERROR('Insufficient Balance', 10, 3)

END

-- Now we can start the transaction

BEGIN TRANSACTION 

UPDATE Accounts

 SET Balance = Balance - @Amount

 WHERE AccountID = @FromAccountID

IF (@@ERROR <> 0)

   BEGIN

    ROLLBACK TRANSACTION   /* if errors - rollback transaction */

    RETURN 

   END

UPDATE Accounts

 SET Balance = Balance + @Amount

 WHERE AccountID = @ToAccountID

IF (@@ERROR <> 0)

   BEGIN

    ROLLBACK TRANSACTION   /* if errors - rollback transaction */

    RETURN 

   END

UPDATE TransactionLog

  SET Successful = 1

  WHERE TransactionID = @TransactionID

IF (@@ERROR <> 0)

   BEGIN

    ROLLBACK TRANSACTION   /* if errors - rollback transaction */

    RETURN 

   END

-- Transaction

COMMIT TRANSACTION

END -- Procedure 

CREATE PROCEDURE [dbo].[LogTransactionAttempt]

(

  @FromAccountID INT,

  @ToAccountID INT, 

  @Amount FLOAT

)

 AS

 BEGIN

  INSERT INTO TransactionLog

  (

    FromAccountID,

    ToAccountID,

    Amount, 

    TransactionDate

  )

  VALUES 

  (

    @FromAccountID,

    @ToAccountID,

    @Amount,

    GETDATE()

  )

  RETURN @@IDENTITY

  END