BREAK (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
SQL analytics endpoint in Microsoft Fabric 👁 Image
Warehouse in Microsoft Fabric 👁 Image
SQL database in Microsoft Fabric

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

• Control-of-Flow Language (Transact-SQL)
• WHILE (Transact-SQL)
• IF...ELSE (Transact-SQL)