CODEWITHSUNDEEP
sql-servert-sql
Breschi
Breschi

Reputation: 13

Infinite WHILE LOOP with T-SQL

I have the following while loop, and it never stops. It ends by printing out 8 forever. Can anybody tell me why?

DECLARE @catID int

SELECT TOP(1)
    @catID = categoryid 
FROM 
    [Production].[Categories] 
ORDER BY 
    categoryid

WHILE @catID IS NOT NULL
BEGIN
    PRINT @catID    --do something

    SELECT TOP(1) 
        @catID = categoryid 
    FROM 
        [Production].[Categories] 
    WHERE 
        categoryid > @catID
    ORDER BY 
        categoryid
END

Upvotes: 1

Views: 2622

Answers (4)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

Your issue must be that at some point your SELECT statement doesn't find any value and doesn't assign anything new to your @catID variable and it keeps exactly the same as it was in previous loop. As a result - you never exit your while statement.

Code snippet to explain that:

DECLARE @catID INT = 5;

DECLARE @Test TABLE
(
    CategoryID INT
);

/**
 * Notice that I do not insert into table intentionally,
 * so that following query doesn't assign anything
 * to @catID variable
 */

SELECT TOP (1) @catID = CategoryID
FROM @Test;

SELECT @catID AS [@catID];

Result:

@catID
-----------
5

This query returns 5, and not NULL as you would expect.

Assigning value using subquery will either assign an actual value or NULL if nothing's found, that means when there's no category higher than @catID it will become NULL and your query will escape loop.

DECLARE @catID INT;

SET @catID = (
    SELECT TOP (1) CategoryID
    FROM Production.Categories
    ORDER BY CategoryID
    );

WHILE @catID IS NOT NULL
BEGIN
    PRINT @catID;

    SET @catID = (
        SELECT TOP (1) CategoryID
        FROM Production.Categories
        WHERE CategoryID > @catID
        ORDER BY CategoryID
        );
END

Upvotes: 2

paparazzo
paparazzo

Reputation: 45106

as for why
this will return zero rows every time
you already have top so there will be no categoryid > @catID

@catID is not assigned a value of null
It is not not assigned at all 

   SELECT TOP(1) 
        @catID = categoryid 
    FROM 
        [Production].[Categories] 
    WHERE 
        categoryid > @catID
    ORDER BY 
        categoryid

fix

DECLARE @catID int 
DECLARE @count int = 1 

SELECT TOP(1)
    @catID = categoryid 
FROM 
    [Production].[Categories] 
ORDER BY 
    categoryid

WHILE @count > 0
BEGIN
    PRINT @catID    --do something

    SELECT TOP(1) 
        @catID = categoryid 
    FROM 
        [Production].[Categories] 
    WHERE 
        categoryid > @catID
    ORDER BY 
        categoryid

    Select @count = select count(*) FROM [Production].[Categories] 
    WHERE categoryid > @catID
END

But your logic has more problems. After you get the top 1 you have it. There are no more top 1 to get.

or 

DECLARE @catID int
DECLARE @catIDnew int

SELECT TOP(1)
    @catID = categoryid 
FROM 
    [Production].[Categories] 
ORDER BY 
    categoryid

WHILE @catID IS NOT NULL
BEGIN
    PRINT @catID    --do something

    @catIDnew = null

    SELECT TOP(1) 
        @catIDnew = categoryid 
    FROM 
        [Production].[Categories] 
    WHERE 
        categoryid > @catID
    ORDER BY 
        categoryid

    @catID =@catIDnew

END

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Here is the logical problem with your code:

  • The loop breaks when @catID becomes NULL
  • This would require that the first record in the query have @catID being NULL,
  • which in turn would require the WHERE clause for that record to be TRUE, returning the record
  • However, if @catID were NULL, then the WHERE condition would never be true: WHERE categoryid > @catID


The reason for this is that comparing the cateogoryid against a NULL value using inequality will always return NULL (unknown), rather than true or false.

How to fix this:

You can try adding an additional check to the WHERE clause which allows the NULL breaking record to be returned:

WHILE @catID IS NOT NULL
BEGIN
    PRINT @catID

    SELECT TOP(1) 
        @catID = categoryid 
    FROM 
        [Production].[Categories] 
    WHERE 
        categoryid > @catID OR @catID IS NULL
    ORDER BY 
        categoryid
END

Upvotes: 4

marc_s
marc_s

Reputation: 755371

You need to re-initialize your variable @catID to NULL before selecting again - since if there's nothing left to select, no row will be returned and the previous value will remain in @catID (and therefore, it never is set to NULL and the IS NOT NULL condition is never met).

Try this:

WHILE @catID IS NOT NULL
BEGIN
    PRINT @catID    --do something

    SET @catID = NULL   -- re-initilatize to NULL

    SELECT TOP(1) 
        @catID = categoryid 
    FROM 
        [Production].[Categories] 
    WHERE 
        categoryid > @catID
    ORDER BY 
        categoryid
END

Upvotes: 2

Related Questions