August 03, 2012 SQL Tutor 0

In the last lesson we learned how to control our SQL transactions. However, we really need to know how to fit this into a stored procedure to take full advantage of it. We’re going to pick up where we left off in the last lesson and add transactions, commits and rollbacks to our existing TransferMoneyToSavings stored procedure.

Here is our stored procedure as it exists right now. Take a look at it and think about where you'd want to start transactions, roll them back and, finally, commit them to the database.

ALTER PROCEDURE TransferMoneyToSavings
(Amount money, AccountID int, CheckingID int, SavingsID int)
AS

DECLARE @current money

SELECT @Current = Total 
FROM Checking
WHERE CheckingID = @CheckingID

PRINT 'You have requested to transfer ' + CONVERT(varchar(50), @amount) + '…'
IF @current => @amount THEN
BEGIN

UPDATE Checking 
SET Total = Total + @Amount
FROM Checking
WHERE CheckingID = @CheckingID

UPDATE Savings
SET Total = Total – @Amount
FROM Savings
WHERE SavingsID = @SavingsID

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, @amount, @CheckingID, @SavingsID, @amount, -(@amount))

PRINT CONVERT(varchar(50), @amount) + ' was successfully transferred!'
END

ELSE
BEGIN
Print 'You cannot transfer more money than you have!'	
Print 'You only have ' + CONVERT(varchar(50), @current) + ' at the moment, but you asked to transfer ' + CONVERT(varchar(50), @ammount) + '…this will not do.'
INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, 0, @CheckingID, @SavingsID, 0, -(0))

END

Where To Begin?

The first thing to think about is where we want to start our transaction. When is the first time we actually write anything to the database here? We print some statements about the user's intent and we also grab their current balance for comparing. But does this have any impact on the database if things fail? Not really. It's probably safe to let that part run no matter what. So the best place to begin our transaction is with the first UPDATE statement that writes to the database.

Where To Commit?

Let's say something goes wrong after we being our transaction. Would we want the log to still write? This would cause some serious confusion. We want to put our ROLLBACK statement in a place where no data will be committed to the database. Thus, the best place for the rollback is after the INSERT into log statement.

ALTER PROCEDURE TransferMoneyToSavings
(Amount money, AccountID int, CheckingID int, SavingsID int)
AS

DECLARE @current money

SELECT @Current = Total 
FROM Checking
WHERE CheckingID = @CheckingID
PRINT 'You have requested to transfer ' + CONVERT(varchar(50), @amount) + '…'
IF @current => @amount THEN
BEGIN

BEGIN TRANSACTION

UPDATE Checking 
SET Total = Total + @Amount
FROM Checking
WHERE CheckingID = @CheckingID

UPDATE Savings
SET Total = Total – @Amount
FROM Savings
WHERE SavingsID = @SavingsID

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, @amount, @CheckingID, @SavingsID, @amount, -(@amount))

COMMIT TRANSACTION

PRINT CONVERT(varchar(50), @amount) + ' was successfully transferred!'
END

ELSE
BEGIN
Print 'You cannot transfer more money than you have!'
	
Print 'You only have ' + CONVERT(varchar(50), @current) + ' at the moment, but you asked to transfer ' + CONVERT(varchar(50), @ammount) + '…this will not do.'

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, 0, @CheckingID, @SavingsID, 0, -(0))

END

Do We Need Rollback?

Rollback is kind of a weird thing. You don't need to use it in every scenario. In this scenario, ROLLBACK can only happen one way. It is implied that if an error is thrown between the BEGIN TRANSACTION and the COMMIT TRANSACTION statement, the ROLLBACK would happen automatically. We don't have to add it to the code.

So, when do you use the ROLLBACK statement? Usually, you use them when we have our own criteria that identify a successful transaction. Some problems don't occur because of a SQL error or a failure of some code but because of data conditions.

Let's say we wanted to check if our transaction was okay after the values had been updated. This isn't the best way to do it, but it demonstrates why we might use ROLLBACK. We'll see further, more relevant examples in the next lesson as well.

UPDATE Checking 
SET Total = Total + @Amount
FROM Checking
WHERE CheckingID = @CheckingID

UPDATE Savings
SET Total = Total – @Amount
FROM Savings
WHERE SavingsID = @SavingsID

DECLARE @newtotal money

SELECT @newtotal = total 
FROM Savings 
WHERE SavingsID = @SavingID

IF @newtotal >= 0 
BEGIN
Print 'You're all good!'
END
ELSE
BEGIN
Print 'You have overdrawn your account!'
END

In this example, we check the new total and make sure it is not a negative number. You might think it's too late to fix it if the problem now, but by adding transactions to this example, we can stop it from going to the database before it's too late.

BEGIN TRANSACTION

UPDATE Checking 
SET Total = Total + @Amount
FROM Checking
WHERE CheckingID = @CheckingID

UPDATE Savings
SET Total = Total – @Amount
FROM Savings
WHERE SavingsID = @SavingsID

DECLARE @newtotal money

SELECT @newtotal = total 
FROM Savings 
WHERE SavingsID = @SavingID

IF @newtotal >= 0 
BEGIN
Print 'You are all good!'

COMMIT TRANSACTION

END
ELSE
BEGIN
Print 'You have overdrawn your account!'

ROLLBACK TRANSATION

END

Now, we open a transaction and it can end one of two ways. Either we close the transaction and commit everything to the database or we call the whole thing off and rollback the transaction.

This is especially handy when we start handling specific errors. Whenever something goes wrong in SQL, there is a way to access the specifics of the error. We might decide to rollback a transaction based on this or other criteria. We'll take a look at this in the next section.