SQL Server: BREAK Statement

This SQL Server tutorial explains how to use the BREAK statement in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server, the BREAK statement is used when you want to exit from a WHILE LOOP and execute the next statements after the loop’s END statement.

Syntax

The syntax for the BREAK statement in SQL Server (Transact-SQL) is:

BREAK;

Parameters or Arguments

There are no parameters or arguments for the BREAK statement.

Note

• You use the BREAK statement to terminate a WHILE LOOP early.
• If there are nested WHILE LOOPs, the BREAK statement will terminate the innermost WHILE LOOP.
• See also the WHILE LOOP and CONTINUE statement.

Example

Let’s look at an example that shows how to use the BREAK statement in SQL Server (Transact-SQL).

For example:

DECLARE @site_value INT;
SET @site_value = 0;

WHILE @site_value <= 10
BEGIN
   IF @site_value = 2
      BREAK;
   ELSE
      PRINT 'Inside WHILE LOOP on TechOnTheNet.com';

   SET @site_value = @site_value + 1;
END;

PRINT 'Done WHILE LOOP on TechOnTheNet.com';
GO

In this BREAK statement example, we will break out of the WHILE LOOP early, if @site_value is equal to 2.