Sunday, August 28, 2022

Cannot use a BREAK statement outside the scope of a WHILE statement

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

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.

The 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 custid FROM [dbo].[Customers]
               WHERE custname = 'Cust_1')
    BREAK
-- 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 38
Cannot use a BREAK statement outside the scope of a WHILE statement.

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

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


Cannot update a timestamp column

 Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.

The timestamp data type is an 8-byte data type that exposes automatically generated, unique binary numbers within a database. It is generally used as a mechanism for version-stamping table rows.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter, the database timestamp, tracks a relative time within a database and not an actual time that can be associated with a clock.
Since the timestamp column is automatically generated by the database and is automatically incremented by the database during an update on the table, this error will be encountered if the timestamp column is manually updated through an UPDATE command.

A table can only have one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.

CREATE TABLE [dbo].[Team] (
    [TeamID]     INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [TeamName]   VARCHAR(50),
    TIMESTAMP )
Insert one record into the table.
INSERT INTO [dbo].[Team] ( [TeamName], [Timestamp] )
VALUES ( 'Miami Marlins', DEFAULT )

select * from [dbo].[Team]
Output:

TeamID

TeamName

TIMESTAMP

1

Miami Marlins

0x00000000000007D1


Let's try to update which will generate the error msg.
UPDATE [dbo].[Team]
SET [Timestamp] = @@DBTS
WHERE [TeamName] = 'Miami Marlins'
Msg 272, Level 16, State 1, Line 11
Cannot update a timestamp column.

The timestamp column is usually used to determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

If there’s really a need to update the timestamp column of a table just to mark that particular record as having been updated, here’s one work around that can be done without really affecting the row but having the timestamp column updated:
UPDATE [dbo].[Team]
SET [TeamName] = [TeamName]
WHERE [TeamName] = 'Miami Marlins'

select * from [dbo].[Team]
Output:

TeamID

TeamName

TIMESTAMP

1

Miami Marlins

0x00000000000007D2