SQL Server loop - how do I iterate over a recordset

Question

how do i scroll the recordset from select?

So say, for example, I have several entries that I want to skip and do with each entry. Here's a primitive version of my choice:

select top 1000 * from dbo.table where StatusID = 7 

thank

Answer 1

Using T-SQL and cursors such as:

 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; 

Answer 2

This is what I did if you need to do something iterative ... but it would be wise to look for the given operations first.

 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 

Answer 3

A small change to sam yi 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 

Answer 4

Another approach if you make good use of temporary tables. I personally checked this and it will not throw any exceptions (even if there is no data in the temp table.)

 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 

Answer 5

You can choose to rank your data and add ROW_NUMBER and count down to zero when re-collecting data.

 -- Get your dataset and rank your dataset by adding a new row_number SELECT TOP 1000 A.*, ROW_NUMBER() OVER(ORDER BY A.ID DESC) AS ROW INTO #TEMPTABLE FROM DBO.TABLE AS A WHERE STATUSID = 7; --Find the highest number to start with DECLARE @COUNTER INT = (SELECT MAX(ROW) FROM #TEMPTABLE); DECLARE @ROW INT; -- Loop true your data until you hit 0 WHILE (@COUNTER != 0) BEGIN SELECT @ROW = ROW FROM #TEMPTABLE WHERE ROW = @COUNTER ORDER BY ROW DESC --DO SOMTHING COOL -- SET your counter to -1 SET @COUNTER = @ROW -1 END DROP TABLE #TEMPTABLE 

Answer 6

this way we can iterate over the table data.

 DECLARE @_MinJobID INT DECLARE @_MaxJobID INT CREATE TABLE #Temp (JobID INT) INSERT INTO #Temp SELECT * FROM DBO.STRINGTOTABLE(@JobID,',') SELECT @_MinJID = MIN(JobID),@_MaxJID = MAX(JobID) FROM #Temp WHILE @_MinJID <= @_MaxJID BEGIN INSERT INTO Mytable ( JobID, ) VALUES ( @_MinJobID, ) SET @_MinJID = @_MinJID + 1; END DROP TABLE #Temp 

STRINGTOTABLE is a user definition function that will parse data separated by commas and a return table. thank