When you are writing TSQL code there are times when you want to perform the same logic over and over again. To accomplish this you can used the WHILE keyword. There are other times when you want to perform a conditional test and depending on the results of the test you execute one code block for the TRUE condition and possibly a different code block when the condition is FALSE. When you need to conditionally execute code based on the outcome of a condition you can used the IF keyword. In this Stairway level I will be discussing how to use the WHILE and IF constructs to control the flow of your TSQL code.
WHILE Keyword Syntax
The WHILE loop syntax is very simple. To review the syntax look at Figure 1.
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE]’; }Figure 1: WHILE keyword syntax.
The execution of the WHILE loop is controlled based on the results of the Boolean_expression. As long as the Boolean_expression evaluates to TRUE then the code block within the WHILE loop is executed and the Boolean_expression is re-evaluated. . Once it is FALSE the code block is no longer executed. The code block within the WHILE loop could be a single TSQL statement or multiple statements grouped within a BEGIN…END block. If you want to programmatically exit the WHILE loop without waiting for the Boolean_expression to evaluate to FALSE, you can do that by issuing the BREAK keyword. Or if you want to skip the rest of the code in the WHILE loop and continue processing from the top of the WHILE loop then you can use the CONTINUE keyword.
WHILE loops can also be nested. The Books Online documentation for the WHILE keyword doesn’t mention the number of nested WHILE loops you can have. But by testing it appears there is a limit. I was able to nest 121 WHILE loops without a problem. But when I tried to nest 122 WHILE loops I got the error show in Results 1.
Msg 191, Level 15, State 1, Line 136 Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
Results 1: Error message when I tried to nest 122 WHILE keywords.
Example of performing WHILE Loop
Let start by looking at a simple WHILE loop example. Let’s assume you want to seed a number table with numbers from 1 to 10. The code in Listing 1 will create and populate a numbers table using a simple WHILE loop.
DECLARE @I INT = 0;
-- create table to populate
CREATE TABLE NumberTBL (NumberValue Int);
WHILE @I < 10
BEGIN
SET @I +=1;
INSERT INTO NumberTBL VALUES (@I);
END
SELECT * FROM NumberTBL;
Listing 1: Basic WHILE Construct
In Listing 1 I process through the WHILE loop as long as the value of the variable @I is less than 10. When it is less than 10 I increment the variable @I by 1 and then insert the value of the variable @I into my NumberTBL table. As long as the WHILE loop condition @I < 10 is TRUE the code within my WHILE loop will be executed. Once my WHILE loop condition evaluates to FALSE the code in the WHILE loop is not executed and execution continues with the first statement after the WHILE loop, which in my example is the SELECT statement at the end of Listing 1.
Example of WHILE Loop using BREAK and CONTINUE
If you have the need to conditionally skip some code within your WHILE loop, but continue with the next iteration you can use the CONTINUE keyword within the WHILE loop. Additionally if you want to exit out of the WHILE loop completely you can do that with the
BREAK keyword. To demonstrate using the BREAK and CONTINUE functionality in a WHILE loop review the code in Listing 2.
SET NOCOUNT ON;
DECLARE @Counter int = 1;
DECLARE @I int;
SET @I = 0;
WHILE @I < 10
BEGIN
SET @I += 1;
SELECT 'Loop 1 Iteration# ' +
CAST (@I as char(2)) +
' Current Counter = ' + CAST(@Counter as CHAR(2));
IF @I = 3
CONTINUE;
IF @I = 5
BREAK;
SET @Counter +=1;
ENDListing 2: Using BREAK and CONTINUE
When I run the code in Listing 2 and using the Query option “Results to Text” I get the output in Result 2.
----------------------------------------- Loop 1 Iteration# 1 Current Counter = 1 ----------------------------------------- Loop 1 Iteration# 2 Current Counter = 2 ----------------------------------------- Loop 1 Iteration# 3 Current Counter = 3 ----------------------------------------- Loop 1 Iteration# 4 Current Counter = 3 ----------------------------------------- Loop 1 Iteration# 5 Current Counter = 4
Result 2: Output produced when running Listing 2
If you review the code in Listing 2 you can see the code executes the CONTINUE keyword when the variable @I is set to 3, and the BREAK statement when variable @I is set to 5. This variable is incremented by 1 every time through my WHILE loop, and the value of @I is also displayed as the “Interation#” in a SELECT statement near the top of the WHILE loop. Additionally I keep track of a counter value @Counter that starts at 1 and is incremented by 1 each time I reach the bottom of the WHILE loop
If you review the results from the Listing 2 code that are found in Results 1 you can make the following observations:
- The “Current NumberValue = 3” text appears twice in the output, with “Iteration#” 3 and 4.
- The last “Iteration#” displayed was 5.
The reason text “Current NumberValue = 3” appears twice in the output displayed in Result 2 is because I have an IF expression that tests if the @I value is equal to 3. When this IF expression test equates to TRUE I execute the CONTINUE keyword. When this CONTINUE keyword is executed all the code in the WHILE loop that is below this CONTINUE keyword is skipped and execution starts at the first statement of the WHILE loop. Therefore when the CONTINUE keyword is executed the SET statement at the bottom of the WHILE loop is not executed, and therefore the @Counter variable is not set to the next sequential value. Additionally the reason that the last “Iteration#” is only 5 is because once the value of the variable @I is 5, the BREAK statement is executed. The BREAK keyword causes processing to continue at the first statement following the WHILE loop.
Examples of Nesting WHILE loops
The WHIL loop can be nested. To demonstrate this let me modify the code in Listing 2 to have another WHILE loop. That modified code can be found in Listing 3.
SET NOCOUNT ON;
DECLARE @Counter int = 1;
DECLARE @Inner int;
DECLARE @Outer int = 0;
WHILE @Outer < 2
BEGIN
SET @Outer +=1;
SET @Inner = 0;
SET @Counter = 1;
WHILE @Inner < 10
BEGIN
SET @Inner += 1;
SELECT 'Outer Loop Iteration# ' +
CAST(@Outer as char(2)) +
'Inner Loop Iteration# ' +
CAST (@Inner as char(2)) +
' Current Number Value = ' + CAST(@Counter as CHAR(2));
IF @Inner = 3
CONTINUE;
IF @Inner = 5
BREAK;
SET @Counter +=1;
END
SELECT 'End of Inner Loop';
END
SELECT 'End of Outer Loop';Listing 3: Example of Nesting WHILE loops.
If you review the code in Listing 3 you can see I enclosed the WHILE loop from Listing 2 within another WHILE loop. This new WHILE loop can be referred to as the outer loop, while the original WHILE loop from Listing 2 can be called the inner loop. I call them outer and inner to reflect the nesting level. The inner loop is nested within the outer. In addition to enclosing the Listing 2 WHILE loop with the outer loop, I also renamed the variable that is used to control WHILE loop, from @I to @Inner. I also changed the message that gets displayed. The message that now get displayed the values of the variables controlling both the inner and outer loop.
When I run the code in Listing 3 I get the output in Result 3.
------------------------------------------------------------------------- Outer Loop Iteration# 1 Inner Loop Iteration# 1 Current Number Value = 1 ------------------------------------------------------------------------- Outer Loop Iteration# 1 Inner Loop Iteration# 2 Current Number Value = 2 ------------------------------------------------------------------------- Outer Loop Iteration# 1 Inner Loop Iteration# 3 Current Number Value = 3 ------------------------------------------------------------------------- Outer Loop Iteration# 1 Inner Loop Iteration# 4 Current Number Value = 3 ------------------------------------------------------------------------- Outer Loop Iteration# 1 Inner Loop Iteration# 5 Current Number Value = 4 ----------------- End of Inner Loop ------------------------------------------------------------------------- Outer Loop Iteration# 2 Inner Loop Iteration# 1 Current Number Value = 1 ------------------------------------------------------------------------- Outer Loop Iteration# 2 Inner Loop Iteration# 2 Current Number Value = 2 ------------------------------------------------------------------------- Outer Loop Iteration# 2 Inner Loop Iteration# 3 Current Number Value = 3 ------------------------------------------------------------------------- Outer Loop Iteration# 2 Inner Loop Iteration# 4 Current Number Value = 3 ------------------------------------------------------------------------- Outer Loop Iteration# 2 Inner Loop Iteration# 5 Current Number Value = 4 ----------------- End of Inner Loop ----------------- End of Outer Loop
Result 3: Output when running Listing 3
By reviewing the output in Result 3. You can see the inner loop was executed twice, once for each execution of the outer loop. The outer loop was only executed twice because the condition on the WHILE loop stopped entering this loop once the variable @Outer was greater than 2.
Syntax of IF Construct
There are times when you want to execute some additional or alternative code based on the evaluation of an expression. This is when the IF logic comes in handy. You might have already noticed I used the IF keyword in its simplest form in Listing 2 and 3 above. By reviewing the syntax of the IF construct in Figure 2 you can see it comes in two forms.
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]Figure 2: IF keyword syntax
One form supports the “if then” logic with which you have an IF followed by a Boolean_expression. When the Boolean_expression evaluates to TRUE the “then” logic is executed, which can be a single statement or a block of statements. Note there is no “THEN” keyword. The second format supported is the “if then else” logic format. When using this format the IF keyword is followed by a Boolean_expression, which is then followed by a statement or block of statements to be executed when the Boolean_expression is TRUE, which is then followed by the ELSE keyword, which is followed by a statement or block of statements that are executed when the Boolean_expression is FALSE.
The IF construct can be nested. There is no limit on the number of nesting levels you can use.
To get a better understanding the different formats of IF construct syntax let me go through several examples.
Examples of using the IF keyword.
For the first example using the IF keyword let’s look at a simple IF statement that test the value of a local variable. The code for this first example can be found in in Listing 4.
DECLARE @I INT = 1;
IF @I=1
PRINT '@I=1';
PRINT 'Done';Listing 4: Testing results of local variable using the IF keyword
When I run the code in Listing 4 I get the results in Result 4.
@I=1 Done
Result 4: The results when running Listing 4.
Here you can see that when the Boolean_expression in the IF keyword evaluates to TRUE, meaning local variable @I was equal to 1, the PRINT statement following the Boolean_expression was executed. Additionally the last PRINT statement in the listing was also executed. If you set the variable @I in Listing 4 to a number other then 1, and then run the code you will find only the “Done” message is printed out.
If you need to perform multiple SQL statements when the Boolean_expression is TRUE you can do that by including a group of TSQL statements in a BEGIN and END block. You can see this in the code in Listing 5.
DECLARE @I int = 1;
IF @I=1
BEGIN
PRINT 'These PRINT statements are only executed when ';
PRINT '@I=1';
ENDListing 5: Executing a code block
When you run the code in Listing 5 both PRINT statements will be executed when the variable @I is equal to 1.
There are times when you want to perform two different code blocks depending on the outcome of the Boolean_expression. This is when the ELSE logic of the IF construct is useful. The code in Listing 6 show how to use the IF keyword in conjunction with the ELSE clause.
DECLARE @I INT = 0;
WHILE @I < 10
BEGIN
IF @I%2 = 0
PRINT CAST (@I AS char(1)) + ' is an EVEN number'
ELSE
PRINT CAST (@I AS char(1)) + ' is an ODD number'
SET @I += 1;
ENDListing 6: Using the ELSE keyword
When I execute the code in listing 6 I get the output in Result 5.
0 is an EVEN number 1 is an ODD number 2 is an EVEN number 3 is an ODD number 4 is an EVEN number 5 is an ODD number 6 is an EVEN number 7 is an ODD number 8 is an EVEN number 9 is an ODD number
Result 5: The results when running Listing 6.
By reviewing the code in Listing 6 you can see I use a WHILE loop that is controlled by variable @I, allowing me to test my IF expression with different values of @I. Each time through the WHILE loop the IF condition is used to test if the @I variable is either ODD or EVEN. If the Boolean_expression evaluates to TRUE I print a message that the variable value is EVEN. If the Boolean_expression is FALSE then the ELSE clause is executed, which prints a message that the variable value is ODD.
You can also nest IF constructs, as shown in Listing 7.
DECLARE @I INT = 0;
WHILE @I < 10
BEGIN
IF @I%2 = 0
IF TAN(@I) > 0
PRINT 'Value ' + CAST(@I as char(1)) + ' is EVEN and the TANGENT is greater than zero'
ELSE
PRINT 'Value ' + CAST(@I as char(1)) + ' is EVEN and the TANGENT is less than or equal to zero'
ELSE
IF TAN(@I) > 0
PRINT 'Value ' + CAST(@I as char(1)) + ' is ODD and the TANGENT is greater than zero'
ELSE
PRINT 'Value ' + CAST(@I as char(1)) + ' is ODD and the TANGENT is less than or equal to zero'
SET @I += 1;
ENDListing 7: Nesting of IF keywords
When I run the code in Listing 7 I get the output in Result 6.
Value 0 is EVEN and the TANGENT is less than or equal to zero Value 1 is ODD and the TANGENT is greater than zero Value 2 is EVEN and the TANGENT is less than or equal to zero Value 3 is ODD and the TANGENT is less than or equal to zero Value 4 is EVEN and the TANGENT is greater than zero Value 5 is ODD and the TANGENT is less than or equal to zero Value 6 is EVEN and the TANGENT is less than or equal to zero Value 7 is ODD and the TANGENT is greater than zero Value 8 is EVEN and the TANGENT is less than or equal to zero Value 9 is ODD and the TANGENT is less than or equal to zero
Result 6: The results when running Listing 7.
If you review the code in Listing 7 you will see that in the WHILE loop I nested the IF constructs by first testing whether or not the @I variable was EVEN, and then after that I nested an IF construct which tests to see if the results of “TAN(@I)” expression is greater than ZERO. Depending on the results of the two different IF conditions for the Boolean_expression,I displayed a different message using different PRINT statements.
Summary
If you need to control the flow of your TSQL code so that you execute a specific code block multiple times, you should consider using a WHILE loop. If you want to conditionally execute code based on whether a condition is TRUE or FALSE, you should consider using an IF statement. The WHILE construct provides you with a looping mechanism within your TSQL code. That repeated code could be a single statement, or a block of statements. The IF construct provides you with a mechanism to execute a single TSQL statement, or a block of TSQL statements based the value of a Boolean expression. Next time you need to control the flow of your TSQL code consider how the WHILE and IF keywords can help.
Question and Answer
In this section you can review how well you have understood using the WHILE and IF keywords by answering the following questions.
Question 1:
Both the WHILE and IF constructs have no limits on the number of nesting levels (TRUE or FALSE)?
- TRUE
- FALSE
Question 2:
In a WHILE loop you can use the CONTINUE keyword. What does it do?
- Jumps to the first statement after the inner WHILE loop and continues processing
- Jumps to the first statement of the inner WHILE loop, and continues processing
- Jumps to the first statement after the outer most WHILE loop within nested WHILE loops, and continues processing
- Jumps to the first statement of the outer most WHILE loop within nested WHILE loops and continues processing
Question 3:
How many times will the stored procedure in the following WHILE loop be executed?
DECLARE @I int = 0;
WHILE @I < 10
BEGIN
EXEC dbo.usp_MySp @I
IF @I > 2
BREAK;
SET @I += 1;
END- 2
- 3
- 4
- 10
Answers:
Question 1:
The correct answer is b. The IF keyword has no limitation on the number of nesting levels. But if you test the number of nesting levels of the WHILE keyword you will find a WHILE loop can only be nest 121 time.
Question 2:
The correct answer is b. The CONTINUE keyword cause the code to jump back to testing the Boolean_expression of the inner WHILE loop, and skips any statements that might follow.
Question 3:
The correct answer is c. The stored procedure dbo.usp_MySP will be executed 4 times. It will first be executed with a variable value of 0, then 1, then 2, and finally with 3. When the variable @I is equal to 3 the Boolean_expression @I > 2 will evaluate to TRUE, which will trigger the BREAK keyword to be executed. When the BREAK keyword is executed the database engine will break out of the WHILE loop.
