|
|
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
|