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

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