Skip to content

while-transact-sql.md

Latest commit

 

History

History
177 lines (131 loc) · 5.58 KB

while-transact-sql.md

File metadata and controls

177 lines (131 loc) · 5.58 KB
title WHILE (Transact-SQL)
description WHILE sets a condition for the repeated execution of a SQL statement or statement block.
author rwestMSFT
ms.author randolphwest
ms.date 07/15/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
WHILE_TSQL
WHILE
CONTINUE_TSQL
CONTINUE
helpviewer_keywords
statements [SQL Server], repeated executions
statement blocks [SQL Server]
repeated statement executions
nested WHILE loops
WHILE keyword
restarting WHILE loop
CONTINUE keyword
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

WHILE (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

Sets a condition for the repeated execution of a 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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDE tsql] 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

[!INCLUDE article-uses-adventureworks]

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

Related content