Flow Control

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:

  • GOTO

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 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!'
	PRINT 'Hey, I <> 1!'


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


	PRINT 'Hey, I = 1!'


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


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
	SELECT @i AS counter

	IF @I < 2
		SELECT '@i is less than 2'


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


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


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.


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
    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.
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three; --This will prevent Branch_Two from executing.
    SELECT 'Jumping To Branch Two.'
    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 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;
CREATE PROCEDURE checkstate @param varchar(11)
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'
    RETURN 1
    RETURN 2;

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.


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.


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.


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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *