As we know that SQL Server does not contain for
loop or do while loop to control of flow for the data processing. In SQL
Server, we can use a T-SQL feature called WHILE loop to set single or multiple
conditions or for the repeated execution of an SQL statement or statement
block.
If we are using T-SQL While loop then inner
statements will be executed repeatedly as long as the specified condition is
true. SQL Server allows that inner execution of statements in the WHILE loop
can be controlled from inside the loop with the BREAK and CONTINUE keywords.
So, we can take some ongoing examples to use
While loop in this tutorial. First of all, we have to know the syntax of the
while loop as given below -
-- Syntax for SQL Server and Azure SQL Database
WHILE Boolean_expression
{ sql_statement |
statement_block |
BREAK | CONTINUE }
-- Syntax for Azure SQL Data Warehouse and Parallel
Data Warehouse
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK }
Arguments
Boolean_expression is an expression that
returns TRUE or FALSE. If the Boolean expression contains a SELECT statement,
the SELECT statement must be enclosed in parentheses.
{sql_statement | statement_block} is any Transact-SQL
statement or statement grouping as defined with a statement block. To define
a statement block, use the control-of-flow keywords BEGIN and END.
BREAK causes an exit from the innermost WHILE loop. Any statements
that appear after the END keyword, marking the end of the loop, are executed.
CONTINUE causes the WHILE loop to restart, ignoring any statements
after the CONTINUE keyword.
|
Example –
Print table of any number
In this example, we are print a table of any
number by using while loop as given below -
---- Declare local variable
---- Incremental number counter
DECLARE @ictr int=1
---- Variable to print for table
DECLARE @inum int=2
---- While condtion
WHILE @ictr<11
BEGIN
---- multiply number and incremental number
PRINT @inum * @ictr
---- set incremental of number
SET @ictr= @ictr + 1
END
Output
Result
1
2
3
4
5
6
7
8
9
10
|
Example – While
loop with BREAK Statement
If
a BREAK statement is executed within a WHILE loop, then it causes the control
to go out of the while loop and start executing the first statement immediately
after the while loop. In this
example, we are printing the table till 100 but set the break condition by
stating that if incremental number is just reached at 1o then T-SQL statement
should be break means statements come out from While loop and just go the next
statements of while loop as given below -
---- Declare local variable
---- Incremental number counter
DECLARE @ictr int=1
---- Variable to print for table
DECLARE @inum int=2
---- While condtion till 100
WHILE @ictr<100
BEGIN
---- multiply number and incremental number
PRINT @inum * @ictr
---- set incremental of number
SET @ictr= @ictr + 1
---- break statement if counter reached till 11
IF @ictr=11
BEGIN
PRINT 'Break: Counter is now at 11'
---- then break statement
BREAK;
END
END
---- next statements after While looop
PRINT 'Next
statement after While loop'
Output
Result
2
4
6
8
10
12
14
16
18
20
Break: Counter is now at 11
Next statement after While loop
|
Example – While
loop with CONTINUE and BREAK Statement
If a CONTINUE statement is executed within a
WHILE loop, then it skips executing the statements following it and transfers
control to the beginning of while loop to start the execution of the next
iteration. In this example, we have just put CONTINUE and BREAK
together but using CONTINUE just before BREAK statement. If CONTINUE comes
before BREAK then BREAK statement will not be executed because CONTINUE will transfer
control to the beginning of while loop to start the execution of the next
iteration as given below -
---- Declare local variable
---- Incremental number counter
DECLARE @ictr int=1
---- Variable to print for table
DECLARE @inum int=2
---- While condtion till 100
WHILE @ictr<100
BEGIN
---- multiply number and incremental number
PRINT @inum * @ictr
---- set incremental of number
SET @ictr= @ictr + 1
---- Continue instruction to program
CONTINUE ----
---- break statement if counter reached till 11
IF @ictr=11 ----
-- This will never executed
BEGIN
PRINT 'Break: Counter is now at 11'
---- then break statement
BREAK;
END
END
---- next statement after While loop
PRINT 'Next statement after While loop'
Output
Result
2
4
6
8
10
…
…
198
Next statement after While loop
|
Conclusion
While loop is very important feature of SQL
server to execute T-SQL statements which are executed repeatedly as long as the
specified business condition is true. We can use it for data tables by
identifying that if the conditional expression is true, the statement is
executed, and then the expression is evaluated again to determine if the
statement should be executed again. This process repeats until the expression
evaluates to false.