SQL Server loop - how do I loop through a set of records

Question

By using T-SQL and cursors like this :

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
    SET @MyCursor = CURSOR FOR
    select top 1000 YourField from dbo.table
        where StatusID = 7      

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

This is what I've been doing if you need to do something iterative... but it would be wise to look for set operations first. Also, do not do this because you don't want to learn cursors.

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select top 1 @TableID = TableID
    from #ControlTable
    order by TableID asc

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

Small change to sam yi's answer (for better readability):

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select @TableID = (select top 1 TableID
                       from #ControlTable
                       order by TableID asc)

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

By using cursor you can easily iterate through records individually and print records separately or as a single message including all the records.

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')

OPEN @BusinessCursor;
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msg = '{
              "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
              "Customer": {
                "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
                "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'",    
              }
            }|'
        print @msg
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END

Just another approach if you are fine using temp tables.I have personally tested this and it will not cause any exception (even if temp table does not have any data.)

CREATE TABLE #TempTable
(
    ROWID int identity(1,1) primary key,
    HIERARCHY_ID_TO_UPDATE int,
)

--create some testing data
--INSERT INTO #TempTable VALUES(1)
--INSERT INTO #TempTable VALUES(2)
--INSERT INTO #TempTable VALUES(4)
--INSERT INTO #TempTable VALUES(6)
--INSERT INTO #TempTable VALUES(8)

DECLARE @MAXID INT, @Counter INT

SET @COUNTER = 1
SELECT @MAXID = COUNT(*) FROM #TempTable

WHILE (@COUNTER <= @MAXID)
BEGIN
    --DO THE PROCESSING HERE 
    SELECT @HIERARCHY_ID_TO_UPDATE = PT.HIERARCHY_ID_TO_UPDATE
    FROM #TempTable AS PT
    WHERE ROWID = @COUNTER

    SET @COUNTER = @COUNTER + 1
END


IF (OBJECT_ID('tempdb..#TempTable') IS NOT NULL)
BEGIN
    DROP TABLE #TempTable
END