The BREAK Keyword in Transact SQL

BREAK is a useful piece of control-of-flow language in SQL Server, but it only works in specific situations.

Break Questions, Answers, and Explanations

1) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

BREAK;

PRINT 2;

Answer:

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

How’d people do?

• Correct: 237 (77%)
• Incorrect: 70 (23%)

I know, it seems like this should work! But, in fact, BREAK only breaks you out of a WHILE loop.

For other purposes, you may want to use RETURN.

2) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
    BREAK;
    PRINT 2

Answer: 2

This one was a little trickier…

• Correct: 202 (66%)
• Incorrect: 105 (34%)

In this case BREAK will cause us to exit that WHILE loop (so we won’t be stuck in it forever). BREAK doesn’t cause the execution of the whole batch to terminate, though, so we go on to the next statement, and PRINT 2 is executed.

3) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
IF 1 = 1
    PRINT 2
    BREAK;

Answer:

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

This one foiled even more folks:

• Correct: 135 (44%)
• Incorrect: 172 (56%)

The key to this one is that we haven’t defined a statement block. That means that the WHILE statement applies to the next statement only. The IF statement also applies to the next statement only. The BREAK is left out in the cold, and it will throw an error if it is not part of a WHILE loop.

But it’s a tricky question, because it might seem like SQL Server would get to the PRINT and be stuck in an infinite loop, never getting to the point that it throws the error. That doesn’t happen - as soon as we execute, the parse process sees the mistake and we get the error right away.

4) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
BEGIN
    BREAK;
    PRINT 2;
END

Answer: Commands completed successfully.

• Correct: 239 (78%)
• Incorrect: 68 (22%)

In this case, the BREAK and the PRINT are within a defined BEGIN / END statement block together, inside a WHILE loop. When we hit the BREAK it exits the loop, never getting to the PRINT statement.