views:

27

answers:

2

SQL Server 2005 BOL says about BREAK:

Exits the innermost loop in a WHILE or IF…ELSE statement.

I don't understand this part about IF…ELSE. It looks like you can put BREAK inside IF. Actually BREAK works only inside WHILE, and control flow pass after loop's END. BREAK not in a WHILE context fires error, and that's completely OK.

My question is - why documentation mention IF...ELSE?

BTW, SQL Server 2000 BOL says only this:

Exits the innermost WHILE loop.

+2  A: 

SQL 2008 BOL says:

Exits the innermost loop in a WHILE statement or an IF…ELSE statement inside a WHILE loop.

which is probably what the SQL 2005 documentation should have said.

Ed Harper
+1 but I do wonder what this 'corrected' 2008 version says that the 2000 version didn't...
AakashM
@AakashM - goo question! I don't have access to a SQL 2000 instance to test how `BREAK` behaved in this context.
Ed Harper
+1  A: 

The SQL2008 explanation still seems somewhat confusing however. To me it implies that break in an IF…ELSE statement inside a WHILE loop would just exit the IF…ELSE. This is not the case.

DECLARE @i INT = 0
WHILE @i<10
BEGIN
    IF(@i=3)
    BEGIN
        PRINT 'i=3'
        BREAK
    END
    ELSE
    BEGIN
        PRINT 'i<>3'
    END
    SET @i = @i+1
END
PRINT 'out of while'

Prints

i<>3
i<>3
i<>3
i=3
out of while
Martin Smith
Yep. At least it works as expected in all SQL Server versions.
Alsin