I am going to write a few articles (tidbits) on controlling the execution flow of statements in a Transaction SQL (T-SQL) program.
Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next.
The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program.
Today, I want to talk about looping for repetition, statement blocks, and conditional execution.
Some programming languages support the following constructs:
- FOR LOOP
- DO WHILE LOOP
- LOOP UNTIL
Microsoft choose to implement the WHILE construct in Transaction SQL (T-SQL). Basically, the program repeats the execution of a statement or statement block until a condition is false.
Statement blocks are created by wrapping code between the BEGIN and END keywords. The block is executed as one set of statements.
The IF statement directs the program to execute a statement only if a condition is true. Any false conditions causes the statements to be skipped.
The example below creates a WHILE loop that counts from 1 to 100. The key part of the code is to increment the counter. Else, the program will become an infinite loop.
The IF statement is inside a statement block. It only displays (PRINT) numbers that are divisible by three.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- -- Show numbers divisible by 3 -- -- Loop counter DECLARE @VAR_CNT INT = 1; -- Count to 100 WHILE (@VAR_CNT <= 100) BEGIN -- Print when modulo is zero IF (@VAR_CNT % 3) = 0 PRINT @VAR_CNT; -- Increment the counter SET @VAR_CNT += 1; END; GO |
The output of this program is listed below.
|
1 2 3 4 5 6 7 8 9 |
output: 3 6 (...) 96 99 |
The long and short of it, most Transaction SQL (T-SQL) programs will use Control Flow Language statements to provide direction to the database transaction engine.
The WHILE statement combined with a statement block (BEGIN/END) allows a database developer to perform a operation a number of times.
The IF statement can be used to conditionally execute a statement. These are just some of the building blocks to write your next program.
Next time, I will be talking about the WAITFOR statement that was introduced in Microsoft SQL Server 2005.
