Skip to content

Latest commit

 

History

History
74 lines (59 loc) · 2.14 KB

break-transact-sql.md

File metadata and controls

74 lines (59 loc) · 2.14 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
BREAK (Transact-SQL)
BREAK (Transact-SQL)
rwestMSFT
randolphwest
11/19/2018
sql
t-sql
reference
BREAK
BREAK_TSQL
exiting innermost loop [SQL Server]
END keyword
ignored statements
BREAK keyword
TSQL
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current||=fabric

BREAK (Transact-SQL)

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

BREAK exits the current WHILE loop. If the current WHILE loop is nested inside another, BREAK exits only the current loop, and control is given to the next statement in the outer loop.

BREAK is usually inside an IF statement.

Examples

Example for SQL Server

Imagine a table where a value is expected when another antecedent process is completed:

WHILE (1=1)
BEGIN
   IF EXISTS (SELECT * FROM ##MyTempTable WHERE EventCode = 'Done')
   BEGIN
      BREAK;  -- 'Done' row has finally been inserted and detected, so end this loop.
   END

   PRINT N'The other process is not yet done.';  -- Re-confirm the non-done status to the console.
   WAITFOR DELAY '00:01:30';  -- Sleep for 90 seconds.
END

Example for Azure Synapse dedicated SQL pool

DECLARE @sleeptimesec int = 1;
DECLARE @startingtime datetime2(2) = getdate();

PRINT N'Sleeping for ' + CAST(@sleeptimesec as varchar(5)) + ' seconds'
WHILE (1=1)
BEGIN
  
    PRINT N'Sleeping.';  
    PRINT datediff(s, getdate(),  @startingtime)

    IF datediff(s, getdate(),  @startingtime) < -@sleeptimesec
        BEGIN
            PRINT 'We have finished waiting.';
            BREAK;
        END
END

See Also