title: SQL loop WHILE IF BREAK
body: <p>In a SQL Server 2012 stored procedure, I have several nested structures. I want to break out of a single layer of them.  </p>
  
  <p>I thought the description of BREAK in the msdn <a href="https://msdn.microsoft.com/en-CA/library/ms181271.aspx" rel="noreferrer">https://msdn.microsoft.com/en-CA/library/ms181271.aspx</a> 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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>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.</p>
  
  <p><i>Exits <strike>the innermost loop in a WHILE statement</strike> or an IF…ELSE statement inside a WHILE loop. Any statements appearing after the END keyword, marking the end of the loop, are executed.</i></p>