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.