When you want to build more intelligent or more flexible T-SQL scripts, you’re going to need to know how to control the flow of the program. You’ll need the code to be able to make simple decisions on what to do next. Here are the commands you’re going to learn:

IF…ELSE
BEGIN…END
BREAK
CONTINUE
WHILE
GOTO
RETURN
WAITFOR

All of these commands allow you to control the flow of execution of Transact-SQL statements, scripts, user-defined functions, and stored procedures. Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Using these commands allow statements to be connected, related to each other, and made interdependent using programming-like constructs.

IF…ELSE

IF…ELSE is the T-SQL version of IF, THEN, ELSE statements in other languages. If you are only going to run one statement as a part of the THEN or ELSE statement blocks, then you don’t have to wrap your statements in the BEGIN…END commands.

DECLARE @i int
SET @i = 1
IF @i = 1
	PRINT 'Hey, I = 1!'
ELSE
	PRINT 'Hey, I <> 1!'

BEGIN…END

BEGIN and END are most often used with other flow control commands. The allow you to join several separate statements into one logical block of statements. You don’t have to have several statements to use it though. Let’s discuss an example that uses the IF statement.

DECLARE @i int

SET @i = 1

IF @i = 1

BEGIN

	PRINT 'Hey, I = 1!'

END

We could have added several lines between the BEGIN…END, but in this case, one should illustrate my point.

BREAK

BREAK, let’s you exit a WHILE or IF…ELSE statement Early. You’ll probably use this most often for cases where an “error” condition occurs. Let’s look at a WHILE statement that would need to BREAK out early.

DECLARE @i int
SET @i = 1

WHILE @i > 0
BEGIN
	SELECT @i AS counter

	IF @I < 2
	BEGIN
		SELECT '@i is less than 2'
		BREAK
	END

END

If it weren’t for the inner IF statement, this loop would never end.

CONTINUE

CONTINUE is the opposite of BREAK. It let’s you resume the loop.

WHILE

WHILE loops are useful, but can be just as dangerous as a trigger, or any other T-SQL construct that you’ll hear people who “know” argue against using.
The reason is, you can create runaway loops that never end. A good rule of thumb is to create a loop with two break conditions, each independent from the other. That way if one case never hits, the other might. Look for my code on @maxIterations…You’ll see what I’m talking about there.

Look back at the code from BREAK, or at the example for GOTO, both contain WHILE loop examples.

GOTO

If you started programming when I did, you probably started with BASIC. The first flow control I ever learned was the GOTO. Basically it lets you jump to a specific point in your code. Most of the time you can replace a GOTO with any other flow control keyword. The following example comes from Microsoft’s BOL.

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.
END
Branch_One:
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three; --This will prevent Branch_Two from executing.
Branch_Two:
    SELECT 'Jumping To Branch Two.'
Branch_Three:
    SELECT 'Jumping To Branch Three.'

In this code, you see that we have a loop that runs from 1 to 10, but on the 4th iteration, it jumps to branch_one, and from there it jumps again to branch_three. At that point, the code ends.

RETURN

RETURN is useful for the unconditional and immediate exit from a query or procedure. Most of the time, you’re just using them in your procedures. RETURN allows you to retun an integer to the calling code.

USE AdventureWorks;
GO
CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'
    RETURN 1
ELSE
    RETURN 2;
GO

In the above example, we have a procedure that takes a parameter, then does a look up in the AdventureWorks.Person.vAdditionalContactInfo view looking for the stateProvince for a contactid = the parameter passed. If the StateProvince is Washington, then it returns 1 to the calling function. If the state is not Washington, then it returns 2.

WAITFOR

Probably the least used flow control keyword is WAITFOR. You can use this to delay processing for an amount of time. There are three main ways to use the WAITFOR command.

WAITFOR DELAY '00:01'

This will wait for the specified period to pass, in this example, we wait for one minute to pass before continuing. You can specify up to 24 hours as a delay.

WAITFOR TIME '18:00'

You can wait until a specified time until continuing. In this example we’re waiting until 6pm before continuing. Think about end of day cleanup functions… You could have them wait until 6pm, since everyone should be gone for the day, and then re-index a table.

The last version of the WAITFOR command uses the service broker. I’m going to wait for a later post to tackle that one. For now, this is plenty for you to learn.

These are your basic flow control commands in T-SQL. Learn when and where to use them. If you have any questions, please, let me know! I’m here to help!

Flow Control