Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : Beals Conjecture - A Search for Counterexamples Using SQL Server
Error Messages
Home > SQL Server Error Messages > Msg 135 - Cannot use a BREAK statement outside the scope of a WHILE statement.
SQL Server Error Messages - Msg 135 - Cannot use a BREAK statement outside the scope of a WHILE statement.

Error Message

Server: Msg 135, Level 16, State 1, Line 1
Cannot use a BREAK statement outside the scope of a 
WHILE statement.

Causes

The BREAK statement exits the innermost loop in a WHILE or IF… ELSE statement. Any statements appearing after the END keyword, marking the end of the loop, are executed. BREAK is frequently, but not always, started by an IF test condition.

As the message suggests, this error message occurs when using the BREAK statement outside a WHILE statement. Here are a few examples of how this error is encountered:

-- BREAK used inside an IF condition but outside a WHILE statement
IF NOT EXISTS (SELECT 'X' FROM [dbo].[User]
               WHERE [UserName] = 'sqlserver')
    BREAK
    
Msg 135, Level 15, State 1, Line 2
Cannot use a BREAK statement outside the scope of a WHILE statement.
-- BREAK used to exit a Stored Procedure
CREATE PROCEDURE [dbo].[usp_GetOrderDetails]
    @OrderID        INT
AS

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Order]
               WHERE [OrderID] = @OrderID)
    BREAK

SELECT * FROM [dbo].[OrderDetail]
WHERE [OrderID] = @OrderID
GO

Msg 135, Level 15, State 1, Line 2
Cannot use a BREAK statement outside the scope of a WHILE statement.
-- Misplaced BREAK Condition
DECLARE @Counter  INT
DECLARE @UserID   INT

SET @Counter = 0
SET @UserID = 1
WHILE EXISTS (SELECT * FROM [dbo].[UserTransaction]
              WHERE [UserID] = @UserID) 
    DELETE TOP (10) FROM [dbo].[UserTransaction]
    WHERE [UserID] = @UserID

    SET @Counter = Counter + 1

    IF @Counter > 10
        BREAK


Msg 135, Level 15, State 1, Line 2
Cannot use a BREAK statement outside the scope of a WHILE statement.

Solution / Work Around:

As the message suggests, the BREAK statement can only be used inside the scope of a WHILE statement. In cases when a set of Transact-SQL statements need to be skipped if a particular condition is not met, instead of using a BREAK statement, the GOTO statement can be used. The GOTO statement alters the flow of execution to a label. The Transact-SQL statement or statements that follow GOTO are skipped and processing continues at the label specified. GOTO statements and labels can be used anywhere within a procedure, batch or statement block and it can be nested as well.

Using the first example earlier, here’s how it will look like replacing the BREAK statement with the GOTO statement:

IF NOT EXISTS (SELECT 'X' FROM [dbo].[User]
               WHERE [UserName] = 'sqlserver')
    GOTO InvalidUser

/*
    Set of Transact-SQL statements to execute here
*/

InvalidUser:

/*
    Another set of Transact-SQL statements to execute here
*/

In the case of exiting a stored procedure if a particular condition is met, instead of using the BREAK statement, the RETURN statement should be used. The RETURN statement exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch or statement block. Statements that follow RETURN are not executed.

Using the second example above, here’s how the stored procedure will look like replacing the BREAK statement with the RETURN statement:

CREATE PROCEDURE [dbo].[usp_GetOrderDetails]
    @OrderID        INT
AS

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Order]
               WHERE [OrderID] = @OrderID)
    RETURN

SELECT * FROM [dbo].[OrderDetail]
WHERE [OrderID] = @OrderID
GO

When executing a statement block or statement group within a WHILE loop, the groups of statements need to be enclosed within a BEGIN END blocks. Otherwise only the first Transact-SQL statement within that statement block will be part of the WHILE loop. The other Transact-SQL statement will be executed after the condition in the WHILE loop is not met anymore or a BREAK statement has been encountered within the WHILE loop.

This is the case for the third example above wherein the statement block is not enclosed within a BEGIN END block. Only the first Transact-SQL statement, in this case the DELETE statement, is part of the WHILE loop. The other Transact-SQL statements are outside the scope of the WHILE loop and will be executed once the WHILE condition is not met.

Here’s how the script will look like with the BEGIN … END block put into place and avoiding the error:

DECLARE @Counter  INT
DECLARE @UserID   INT

SET @Counter = 0
SET @UserID = 1
WHILE EXISTS (SELECT * FROM [dbo].[UserTransaction]
              WHERE [UserID] = @UserID)
BEGIN
    DELETE TOP (10) FROM [dbo].[UserTransaction]
    WHERE [UserID] = @UserID

    SET @Counter = Counter + 1

    IF @Counter > 10
        BREAK
END
Related Articles :