As you've realized by now, SQL is very different than most programming languages. Most programming languages deal with sets of linear instructions that kick off when something happens. For example, a user clicks a button that says print, a series of commands kick off to print the document. It might also check to see if the printer is on or available before proceeding.

So far, we've only looked at commands that return data in SQL. This is SQL's main purpose but sometimes we need to return data based on decisions that SQL can make for us. Consider our example from the previous lesson, we built a nice little stored procedure to transfer money, but as it is right now, there are no rules about when it's ok to transfer money and how much money. At the very least, a real ATM would check the amount you requested against your current balance.

Today, we'll learn how to control what SQL does based on the value of a variable or mathematical equation and change the path of execution. If you've ever programmed in C, Visual Basic, VBA or other procedural languages, this code might look very familiar to you.

The IF ELSE Statement

In SQL, the basic construct for controlling the flow of logic is called an IF ELSE statement. It's very similar to the IF ELSE you may have seen in other language. There are numerous ways to write an IF ELSE. We'll look at them one at a time.

The Simple One-Liner

Syntax:

IF [some expression] 
   [some sql]

Example:

IF 1 = 2 
    Print 'Math is Broken!'

This version doesn't really divert your flow of logic to another path. It just makes a conditional step happen under certain circumstances. Take a look at this logic diagram that helps us understand what happens.

sql-if-then

The expression is the item that SQL looks at and decides if it's true or false. It's the same kind of thing we always put into WHERE clauses. As you can see here the expression it's looking at is 1 = 2. Does 1 = 2? The answer for the computer is false. It does not, therefore the statement after the conditional clauses (1=2) will not get executed. Let's look at the next version and how to add something when the expression is not true.

The ELSE Statement

Sometimes we want to have something happen if the expression is true and a different thing happen if it's not true. This is where we employ the ELSE statement.

Syntax:

IF [some expression] 
   [SQL that happens if true] 
ELSE 
   [SQL that happens if not true]

Example:

IF 1 = 2 
   Print 'Math is Broken!' 
ELSE 
   Print 'Math is good.'
sql-if-then

Now we have something that happens regardless of the outcome of our SQL statement. This is probably one of the most common ways we use the IF ELSE statement. However, we still need something more than this to fix our problems with the transfer stored procedure. Let's take a look at our stored procedure again and think about what it's missing.

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

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))

Basically, we don't want any of the SQL here to happen if the user does not have enough money in his checking account to cover the amount being transferred. We could use a simple one-liner IF ELSE but all it would really do is warn the user that they're asking for something wrong. What we really need to do is skip the entire block of updates and inserts if something goes wrong. We might also want to insert a record of the failed transaction into the log. This is where we need a much more complicated verison of the IF ELSE statement. We need one that has several commands for either choice.

Mult-Line IF ELSE Statement

Let's start by figuring out the logic of this problem. Upon execution of the stored procedure, we'll know how much the user is asking for, but we'll also need to get their current savings balance to compare the value of @Amount against. Here's our logic diagram. The pieces we need to add have green backgrounds.

sql-if-then

First off, we need to add code to get the user's current checking account balance into a variable. The new code is bold.

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

DECLARE @current money

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

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))

Now we have a variable to compare against @amount, which is the variable that says how much we're moving over. Next comes the IF ELSE statement. Here's the syntax for a multi-line IF ELSE statement.

Syntax:

IF [expression]
BEGIN
[sql statements]
[sql statements]
END
ELSE
BEGIN
[sql statements]
[sql statements]
END

With this syntax, we define the beginning and end points of the conditional sections with the words BEGIN and END. Here's how this gets incorporated into our current code.

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

DECLARE @current money

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

IF (@current => @amount)
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))

END

Now, the updates only happen if the user's current balance is more or equal to the amount they are requesting to move. The next thing we can do, is add our message and log as part of an ELSE statement. Since this also takes two steps, we'll want to have the BEGIN and END statements again.

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

DECLARE @current money

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

IF (@current => @amount)
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))
END

ELSE
BEGIN

Print 'You cannot transfer more money than you have!'	

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

END

Now, we've reproduced the logic shown in our flow chart. We only print the error message if the math does not add up. We only make the transfer if it makes sense to do so. Now, our stored procedure is getting closer to being a truly powerful and intelligent bit of code!

Formatting SQL

You might have noticed that we indented the lines inside the BEGIN and END blocks. This is a standard way of denoting the flow of logic through the program. It's easy to tell which statements happen inside the IF-ELSE block and which ones don't. This helps others who may read or one day change your code to understand exactly what you're doing. These are not hard and fast rules, but they are very helpful.

What if we'd like to put amounts of @current and @amount into the error message? Well, we're going to need to know how to treat numbers as text and a few other things to pull this off. Our next lesson will deal with converting variables into different types of data.

Categories : SQL
Tags : t-sql, SQL Basic


author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories