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
