Monday, February 26, 2018

TSQL - While Loop in SQL Server


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.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands