Why does SQL Continue Statement create an Endless loop instead of Continuing?

Question

Trying to use the CONTINUE statement in a simple loop.

The loop works without the IF=16 and CONTINUE statement but doesn't work with them

 DECLARE @counter INT
 DECLARE @maxLocations INT
 DECLARE @numLocations INT

 SET @maxLocations = (SELECT MAX(calculated_host_listings_count) FROM [PracticeDB].[dbo].[AB_NYC_2019$]) 
 SET @counter = 0

WHILE @counter<=@maxLocations
    BEGIN
        SET @numLocations = 
            (SELECT COUNT(*) FROM [PracticeDB].[dbo].[AB_NYC_2019$] WHERE calculated_host_listings_count = @counter)

        IF(@numLocations=16)
        BEGIN
            CONTINUE
        END 

        ELSE
        BEGIN
            PRINT CAST(@numLocations AS VARCHAR(6)) + ' rentals with ' + 
                CAST(@counter as VARCHAR(6)) + ' host listings'
        END

        SET @counter += 1
    END

Result without IF(@numlocations=16) CONTINUE

0 rentals with 0 host listings
32303 rentals with 1 host listings
6658 rentals with 2 host listings
2853 rentals with 3 host listings
1440 rentals with 4 host listings
845 rentals with 5 host listings
570 rentals with 6 host listings
399 rentals with 7 host listings
416 rentals with 8 host listings
234 rentals with 9 host listings
210 rentals with 10 host listings
110 rentals with 11 host listings
180 rentals with 12 host listings
130 rentals with 13 host listings
70 rentals with 14 host listings
75 rentals with 15 host listings
16 rentals with 16 host listings
68 rentals with 17 host listings

Result with IF & Continue (original code above) goes into an endless loop but when I stop I get results up until before the IF result (16)

0 rentals with 0 host listings
32303 rentals with 1 host listings
6658 rentals with 2 host listings
2853 rentals with 3 host listings
1440 rentals with 4 host listings
845 rentals with 5 host listings
570 rentals with 6 host listings
399 rentals with 7 host listings
416 rentals with 8 host listings
234 rentals with 9 host listings
210 rentals with 10 host listings
110 rentals with 11 host listings
180 rentals with 12 host listings
130 rentals with 13 host listings
70 rentals with 14 host listings
75 rentals with 15 host listings

Why you don't do the extraction with a query instead of implementing cursor loops ? Cursor loops should be always avoided if possible — DarioN1
– DarioN1, Commented Feb 13, 2020 at 13:26
continue skips the rest of the code in the loop and goes on a new iteration. In your code, it also skips @counter increment, so loop goes infinitely. So... works as expected. Check the docs about what continue means in T-SQL. — Roger Wolf
– Roger Wolf, Commented Feb 13, 2020 at 13:28
Was your intention to break out of the loop at sweet 16? — HABO
– HABO, Commented Feb 13, 2020 at 13:40
@MarcGuillot True - but a counter-based while loop is really just a more error-prone cursor. — SMor
– SMor, Commented Feb 13, 2020 at 14:02

Esat Erkec · Accepted Answer · 2020-02-13 13:54:47Z

4

CONTINUE statement is used in the SQL WHILE loop in order to stop the current iteration of the loop when certain conditions occur, and then it starts a new iteration from the beginning of the loop SQL WHILE loop with simple examples

According to your issue, you need to iterate the counter variable after or before the CONTINUE statement. IMHO your query will be like this ;

DECLARE @counter INT
 DECLARE @maxLocations INT
 DECLARE @numLocations INT

 SET @maxLocations = (SELECT MAX(calculated_host_listings_count) FROM [PracticeDB].[dbo].[AB_NYC_2019$]) 
 SET @counter = 0

WHILE @counter<=@maxLocations
    BEGIN
        SET @numLocations = 
            (SELECT COUNT(*) FROM [PracticeDB].[dbo].[AB_NYC_2019$] WHERE calculated_host_listings_count = @counter)

        IF(@numLocations=16)
        BEGIN
            SET @counter += 1 --Changed part
            CONTINUE
        END 

        ELSE
        BEGIN
            PRINT CAST(@numLocations AS VARCHAR(6)) + ' rentals with ' + 
                CAST(@counter as VARCHAR(6)) + ' host listings'
        END

        SET @counter += 1
    END

edited Feb 13, 2020 at 13:54

answered Feb 13, 2020 at 13:34

Esat Erkec

1,79411 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MarkITright Over a year ago

I thought the continue loop would just skip the print part of the loop and still set the counter += 1! Thank you for the answer :)

Collectives™ on Stack Overflow

Why does SQL Continue Statement create an Endless loop instead of Continuing?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related