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 Here is the logical problem with your code:
@catID becomes NULL@catID being NULL,WHERE clause for that record to be TRUE, returning the record@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 
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 
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 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 