Why my T-SQL (WHILE) does not work?

Question

Hello, In my code, I need to test whether specified column is null and the most close to 0 as possible (it can holds numbers from 0 to 50) so I have tried the code below. It should start from 0 and for each value test the query. When @Results gets null, it should return. However, it does not work. Still prints 0.

declare @hold int
declare @Result int
set @hold0
set @Result=0

WHILE (@Result!=null)
BEGIN
select @Result=(SELECT Hold from Numbers WHERE Name='Test' AND Hold=@hold)
set @hold=@hold+1
END

print @hold

Answer 1

First, you can't test equality of NULL. NULL means an unknown value, so you don't know whether or not it does (or does not) equal any specific value. Instead of @Result!=NULL use @result IS NOT NULL

Second, don't use this kind of sequential processing in SQL if you can at all help it. SQL is made to handle sets, not process things sequentially. You could do all of this work with one simple SQL command and it will most likely run faster anyway:

SELECT
    MIN(hold) + 1
FROM
    Numbers N1
WHERE
    N1.name = 'Test' AND
    NOT EXISTS
    (
        SELECT
            *
        FROM
            Numbers N2
        WHERE
            N2.name = 'Test' AND
            N2.hold = N1.hold + 1
    )

The query above basically tells the SQL Server, "Give me the smallest hold value plus 1 (MIN(hold) + 1) in the table Numbers where the name is test (name = 'Test') and where the row with name of 'Test' and hold of one more that that does not exist (the whole "NOT EXISTS" part)". In the case of the following rows:

Name      Hold
--------  ----
Test      1
Test      2
NotTest   3
Test      20

SQL Server finds all of the rows with name of "Test" (1, 2, 20) then finds which ones don't have a row with name = Test and hold = hold + 1. For 1 there is a row with Test, 2 that exists. For Test, 2 there is no Test, 3 so it's still in the potential results. For Test, 20 there is no Test, 21 so that leaves us with:

Name      Hold
--------  ----
Test      2
Test      20

Now SQL Server looks for MIN(hold) and gets 2 then it adds 1, so you get 3.

SQL Server may not perform the operations exactly as I described. The SQL statement tells SQL Server what you're looking for, but not how to get it. SQL Server has the freedom to use whatever method it determines is the most efficient for getting the answer.

The key is to always think in terms of sets and how do those sets get put together (through JOINs), filtered (through WHERE conditions or ON conditions within a join, and when necessary, grouped and aggregated (MIN, MAX, AVG, etc.).

Answer 2

have you tried

WHILE (@Result is not null)
BEGIN
select @Result=(SELECT Hold from Numbers WHERE Name='Test' AND Hold=@hold)
set @hold=@hold+1
END

Answer 3

Try this:

declare @hold int
declare @Result int
set @hold=0
set @Result=0
declare @max int
SELECT @max=MAX(Hold) FROM Numbers

WHILE (@hold <= @max)
BEGIN
   select @Result=(SELECT Hold from Numbers WHERE Name='Test' AND Hold=@hold)

   set @hold=@hold+1
END

print @hold

While is tricky in T-SQL - you can use this for (foreach) looping through (temp) tables too - with:

-- Foreach with T-SQL while
DECLARE @tempTable TABLE (rownum int IDENTITY (1, 1) Primary key NOT NULL, Number int)
declare @RowCnt int
declare @MaxRows int
select @RowCnt = 1
select @MaxRows=count(*) from @tempTable
declare @number int

while @RowCnt <= @MaxRows
begin
    -- Number from given RowNumber
    SELECT @number=Number FROM @tempTable where rownum = @RowCnt

    -- next row
    Select @RowCnt = @RowCnt + 1
end

Answer 4

Here's a more advanced version of Tom H.'s query:

SELECT MIN(N1.hold) + 1
FROM Numbers N1
LEFT OUTER JOIN Numbers N2
    ON N2.Name = N1.Name AND N2.hold = N1.hold + 1
WHERE N1.name = 'Test' AND N2.name IS NULL

It's not as intuitive if you're not familiar with SQL, but it uses identical logic. For those who are more familiar with SQL, it makes the relationship between N1 and N2 easier to see. It may also be easier for the query optimizer to handle, depending on your DBMS.