SQL loop WHILE IF BREAK

Question

In a SQL Server 2012 stored procedure, I have several nested structures. I want to break out of a single layer of them.

I thought the description of BREAK in the msdn https://msdn.microsoft.com/en-CA/library/ms181271.aspx was on my side. But I'm getting some odd behaviour while running it in single step through debug. I say odd because it isn't consistent. Sometimes it escapes to the layer I expect.. sometimes it skips a couple.

WHILE ... BEGIN
  stuff1
  IF...BEGIN
    stuff2
    WHILE ... BEGIN
      stuff3
      IF .... BEGIN
        stuff4
        IF @NumberRecords=0 BREAK
        stuff5
      END
      --stuff6
      if @NumberRecords=0 and @loopBOMRowCount=@ResultsSOloopstart-1 break
      --on the last occasion I observed, @loopBOMRowCount was 6 and @ResultsSOloopstart 71 and it never highlighted this section, either way

      SET @loopBOMRowCount = @loopBOMRowCount + 1
    END
    stuff7 --nothing actually here
  END
  --stuff8
  SET @periodloopcount=@periodloopcount+1 
  --this is where it ended up highlighting on that last occasion
END
stuff9

So if NumberRecords=0, then the next op should be the if at stuff6, right? Even if stuff4 includes, say, an INSERT INTO table from an EXEC call to a stored procedure? Nothing should be able to confuse the stack out of its layers?

And yes, I realize that's ugly SQL. Most of the instructions are edits on two temp tables and I was avoiding passing them back and forth to stored procedures that would otherwise clean the code.

EDIT

I managed to get it to route the way I desired by adding a dummy WHILE loop around the inner IF I want to break out of first. But I'd really like to know how I'm misinterpreting the msdn info. It seems to say a BREAK should break out of an IF, as long as it has an END statement.

Exits the innermost loop in a WHILE statement or an IF…ELSE statement inside a WHILE loop. Any statements appearing after the END keyword, marking the end of the loop, are executed.

Answer 1

I agree the documentation is a bit confusing. This line seems to suggest you can BREAK out of an IF.

Exits the innermost loop in a WHILE statement or an IF…ELSE statement inside a WHILE loop. 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.

However that is not the case. BREAK exits the inner most WHILE from its position. The key part of the documentation is "any statements appearing after the END keyword, marking the end of the loop, are executed.".

This example demonstrates this.

Example 1

DECLARE @X INT = 1;

PRINT 'Start'

/* WHILE loop required to use BREAK.
 */
WHILE @X = 1
BEGIN

    /* Outer IF.
     */
    IF 1 = 1 
    BEGIN
        /* Inner IF.
         */
        IF 2 = 2
        BEGIN
            BREAK
            PRINT '2'
        END

        PRINT '1'
    END

    SET @X = @X + 1;
END

PRINT 'End'

Only the Start and End text is printed. 1 is not printed because the BREAK exists the WHILE.

You can also see this behaviour here:

Example 2

/* Anti-Pattern.
 * Breaking outside a WHILE is not allowed.
 */
IF 1 = 1 
BEGIN
    BREAK 
    PRINT 1
END

This query returns the error:

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

Answer 2

If you really wanted to break out of the IF statement , to print "Start,1,End" as in the example above, you could do the following

DECLARE @X INT = 1;

PRINT 'Start'

/* WHILE loop required to use BREAK.
 */
WHILE @X = 1
BEGIN

    /* Outer IF.
     */
    IF 1 = 1 
    BEGIN
        /* Inner IF.
         */
        IF 2 = 2
        BEGIN
            GOTO skip2
            PRINT '2'
        END
        skip2:

        PRINT '1'
    END

    SET @X = @X + 1;
END

PRINT 'End'

Now while you could use this to handle the OP's example using the following

WHILE ... BEGIN
  stuff1
  IF...BEGIN
    stuff2
    WHILE ... BEGIN
      stuff3
      IF .... BEGIN
        stuff4
        IF @NumberRecords=0
            GOTO startstuff6
        stuff5
      END
      startstuff6:
      --stuff6
      if @NumberRecords=0 and @loopBOMRowCount=@ResultsSOloopstart-1
        GOTO startstuff7
      --on the last occasion I observed, @loopBOMRowCount was 6 and @ResultsSOloopstart 71 and it never highlighted this section, either way

      SET @loopBOMRowCount = @loopBOMRowCount + 1
    END
    startstuff7:
    stuff7 --nothing actually here
  END
  --stuff8
  SET @periodloopcount=@periodloopcount+1 
  --this is where it ended up highlighting on that last occasion
END
stuff9

It's generally considered better approach to inverse your boolean logic, for example:

IF NOT @NumberRecords=0
BEGIN
        stuff5
END