|
|
Error Message
Server: Msg 136, Level 15, State 1, Line 2
Cannot use a CONTINUE statement outside the scope of a
WHILE statement.
Causes
The CONTINUE statement, which is one of SQL Server’s Transact-SQL control-of-flow language, restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored. CONTINUE is frequently, but not always, opened by an IF condition.
|
As the message suggests, this error message occurs when using the CONTINUE statement outside a WHILE statement. Here are a few examples of how this error may be encountered:
-- CONTINUE used inside an IF condition but outside a WHILE statement
IF EXISTS (SELECT 'X' FROM [dbo].[User]
WHERE [UserName] = 'sqlserver')
CONTINUE
Msg 136, Level 15, State 1, Line 3
Cannot use a CONTINUE statement outside the scope of a WHILE statement.
-- CONTINUE used in a Stored Procedure for Execution Continuation
CREATE PROCEDURE [dbo].[usp_GetOrderDetails]
@OrderID INT
AS
IF EXISTS (SELECT 'X' FROM [dbo].[Order]
WHERE [OrderID] = @OrderID)
CONTINUE
ELSE
RETURN
SELECT * FROM [dbo].[OrderDetail]
WHERE [OrderID] = @OrderID
GO
Msg 136, Level 15, State 1, Procedure usp_GetOrderDetails, Line 7
Cannot use a CONTINUE statement outside the scope of a WHILE statement.
-- Misplaced CONTINUE Condition
DECLARE cUsers CURSOR LOCAL STATIC FOR
SELECT [UserName] FROM [dbo].[Users]
DECLARE @UserName VARCHAR(20)
DECLARE @UserCount INT
SET @UserCount = 0
OPEN cUsers
FETCH NEXT FROM cUsers INTO @UserName
WHILE @@FETCH_STATUS = 0
SET @UserCount = @UserCount + 1
-- Don't Delete User if User Has Transactions
IF EXISTS (SELECT 'X' FROM [dbo].[UserTransactions]
WHERE [UserName] = @UserName)
CONTINUE
DELETE FROM [dbo].[Users] WHERE [UserName] = @UserName
FETCH NEXT FROM cUsers INTO @UserName
CLOSE cUsers
DEALLOCATE cUsers
GO
Msg 136, Level 15, State 1, Line 17
Cannot use a CONTINUE statement outside the scope of a WHILE statement.
Solution / Work Around:
As the message suggests, the CONTINUE 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 CONTINUE 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 CONTINUE statement with the GOTO statement:
IF EXISTS (SELECT 'X' FROM [dbo].[User]
WHERE [UserName] = 'sqlserver')
GOTO ValidUser
/*
Set of Transact-SQL statements to execute here
*/
ValidUser:
/*
Another set of Transact-SQL statements to execute here
*/
Using the second example above, here’s how the stored procedure will look like removing the CONTINUE statement and still perform the same task:
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 SET @UserCount = @UserCount + 1 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 cUsers CURSOR LOCAL STATIC FOR
SELECT [UserName] FROM [dbo].[Users]
DECLARE @UserName VARCHAR(20)
DECLARE @UserCount INT
SET @UserCount = 0
OPEN cUsers
FETCH NEXT FROM cUsers INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @UserCount = @UserCount + 1
-- Don't Delete User if User Has Transactions
IF EXISTS (SELECT 'X' FROM [dbo].[UserTransactions]
WHERE [UserName] = @UserName)
CONTINUE
DELETE FROM [dbo].[Users] WHERE [UserName] = @UserName
FETCH NEXT FROM cUsers INTO @UserName
END
CLOSE cUsers
DEALLOCATE cUsers
GO
|