WHILE (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE
loop can be controlled from inside the loop with the BREAK
and CONTINUE
keywords.
Transact-SQL syntax conventions
Syntax
Syntax for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric.
WHILE boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }
Syntax for Azure Synapse Analytics and Analytics Platform System (PDW).
WHILE boolean_expression
{ sql_statement | statement_block | BREAK }
Arguments
boolean_expression
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 }
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
Restarts a WHILE
loop. Any statements after the CONTINUE
keyword are ignored. CONTINUE
is frequently, but not always, opened by an IF
test. For more information, see Control-of-Flow.
Remarks
If two or more WHILE
loops are nested, the inner BREAK
exits to the next outermost loop. All the statements after the end of the inner loop run first, and then the next outermost loop restarts.
Examples
The Transact-SQL code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
A. Use BREAK and CONTINUE with nested IF...ELSE and WHILE
In the following example, if the average list price of a product is less than $300, the WHILE
loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE
loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE
loop and prints a message.
USE AdventureWorks2022;
GO
WHILE (
SELECT AVG(ListPrice)
FROM Production.Product
) < $300
BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice)
FROM Production.Product
IF (
SELECT MAX(ListPrice)
FROM Production.Product
) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';
B. Use WHILE in a cursor
The following example uses @@FETCH_STATUS
to control cursor activities in a WHILE
loop.
DECLARE @EmployeeID AS NVARCHAR(256)
DECLARE @Title AS NVARCHAR(50)
DECLARE Employee_Cursor CURSOR
FOR
SELECT LoginID,
JobTitle
FROM AdventureWorks2022.HumanResources.Employee
WHERE JobTitle = 'Marketing Specialist';
OPEN Employee_Cursor;
FETCH NEXT
FROM Employee_Cursor
INTO @EmployeeID,
@Title;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ' + @EmployeeID + ' ' + @Title
FETCH NEXT
FROM Employee_Cursor
INTO @EmployeeID,
@Title;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
C: WHILE loop
In the following example, if the average list price of a product is less than $300, the WHILE
loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE
loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE
loop.
WHILE (
SELECT AVG(ListPrice)
FROM dbo.DimProduct
) < $300
BEGIN
UPDATE dbo.DimProduct
SET ListPrice = ListPrice * 2;
SELECT MAX(ListPrice)
FROM dbo.DimProduct
IF (
SELECT MAX(ListPrice)
FROM dbo.DimProduct
) > $500
BREAK;
END