Post
Ask Question

While Loop In SQL Server

Introduction

 
In this article, we will learn how to use looping in SQL Server. In SQL Server, the For() loop is not used. So, for looping, we will use “WHILE” loop in SQL Server. The While statement is a control-flow statement that allows you to execute a statement block repeatedly or group of statements while a given condition is true. It tests the condition before executing the loop body.
 
The syntax for the While Loop in SQL Server (T-SQL) is,
  1. WHILE <Boolean_Expression>  
  2. BEGIN  
  3.    <Statements>  
  4. END  
In the above syntax:
 
The Boolean_Expression is an expression that evaluates to TRUE or FALSE. If the condition evaluates to TRUE, the loop body is executed. If the condition evaluates to FALSE, the loop is terminated.
 
Statements are any T-SQL statement or a set of T-SQL statements to execute each pass through the loop. A statement block is defined using the BEGIN...END statement. Inside the WHILE loop, we must put some condition to make the Boolean_expression returns FALSE at some points. Otherwise, you will have an indefinite loop.
 
To exit from the WHILE LOOP, we use the BREAK statement.
 
To restart the WHILE LOOP from the beginning, we use the CONTINUE statement.
 
Example
 
Let's look at an example that shows how to use a WHILE LOOP in SQL Server (Transact-SQL).
 
For example, 
  1. DECLARE @numValue INT = 0  
  2.   
  3. WHILE @numValue < 5  
  4. BEGIN  
  5.    SET @numValue = @numValue + 1  
  6.    PRINT 'Inside WHILE LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'  
  7. END  
  8.   
  9. PRINT 'WHILE LOOP End !!'  
Result
 
WHILE Loop In SQL Server
 
In this above example, the loop would terminate once the @numValue is equal to 5. Here, we checked if the @numValue is less than 5. If it was not, we increased its value by one and printed out the @numValue. After five iterations, the condition of the WHILE clause evaluates to FALSE because the @numValue is 5 and the loop stopped.
 

BREAK Statement

 
The BREAK statement is used when you want to exit from a WHILE LOOP. If there are nested WHILE Loops, the BREAK statement will terminate the innermost WHILE LOOP.
 
The syntax for the BREAK statement in SQL Server (T-SQL) is -
 
BREAK
 
For example, 
  1. DECLARE @numValue INT = 0  
  2.   
  3. WHILE @numValue < 5  
  4. BEGIN  
  5.     SET @numValue = @numValue + 1  
  6.   
  7.     IF @numValue = 3  
  8.     BEGIN  
  9.         PRINT 'Use BREAK in LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'  
  10.         BREAK  
  11.     END  
  12.   
  13.     PRINT 'Inside WHILE LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'  
  14. END  
  15.   
  16. PRINT 'WHILE LOOP End !!'  
Result
 
WHILE Loop In SQL Server
 
In this above example, the loop would terminate once the @numValue is equal to 3. Here, if the @numValue is equal to 3, we will break out of the WHILE LOOP.
 

CONTINUE Statement

 
The CONTINUE statement is used when you want a WHILE LOOP to execute again. It will ignore any statements after the CONTINUE statement.
 
The syntax for the COUNTINUE statement in SQL Server (T-SQL) is:
 
CONTINUE
 
For example, 
  1. DECLARE @numValue INT = 0  
  2.   
  3. WHILE @numValue < 5  
  4. BEGIN  
  5.     SET @numValue = @numValue + 1  
  6.     PRINT 'Inside WHILE LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'  
  7.     CONTINUE  
  8.     IF @numValue = 3 -- This will never executed  
  9.     BEGIN  
  10.         PRINT 'Use BREAK in LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'  
  11.         BREAK  
  12.     END  
  13. END  
  14.   
  15. PRINT 'WHILE LOOP End !!'  
Result
 
WHILE Loop In SQL Server
 
In this above example, the loop would terminate once the @numValue is equal to 5, because it will ignore any statements after the CONTINUE statement.


Similar Articles