SQL WHILE IF BREAK

Question

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

I thought the BREAK description in msdn https://msdn.microsoft.com/en-CA/library/ms181271.aspx was on my side. But I get some weird behavior when it starts in one step using debugging. I speak strange because it is consistent. Sometimes it eludes the layer that I expect. Sometimes he 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 if if on stuff6, right? Even if stuff4 includes, say, an INSERT INTO table from an EXEC call into a stored procedure? Nothing should confuse a stack of its layers?

And yes, I understand that ugly SQL. Most instructions are edited in two temporary tables, and I avoided passing them back and forth to stored procedures that would otherwise clear the code.

EDIT

I was able to set up the route the way I wanted by adding a dummy WHILE loop around the inner IF, which I want to break out of the first. But I would love to know how I misinterpret msdn information. It seems BREAK should exit IF if it has an END statement.

This leaves the innermost loop in the WHILE statement or the IF ... ELSE statement inside the WHILE loop. Any statements appearing after the END keyword that indicates the end of the loop are executed.

Answer 1

I agree that the documentation is a bit confusing. This line seems to offer you the BREAK of IF.

Exiting the innermost loop in the WHILE statement or IF ... ELSE statement inside the WHILE loop. Any statements that appear after the END keyword that mark the end of the loop. BREAK often, but not always, begins with an IF test.

However, it is not. BREAK emerges from the innermost position of WHILE from his position. A key part of the documentation is "any statements that appear after the END keyword , marking the end of the loop .".

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 text "Start and End" is printed. 1 is not printed because BREAK exists WHILE.

You can also see this behavior here:

Example 2

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

This request returns an error:

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

Answer 2

If you really wanted to break out of the IF statement to type "Start, 1, End", as in the example above, you can 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 that you can use this to handle the OP 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 

In general, the best approach to the inversion of logical logic was considered, for example:

 IF NOT @NumberRecords=0 BEGIN stuff5 END