Nested Cursors in T-SQL

We have two sample tables  :

• tracks – which store audio track info
• matches – table which stores tracks matched in external data

As you can see in the table rows below, the Id field of the track table is the foreign key in the matches table in the form of trackId. The example T-SQL code shows how to iterate rows in matches and then fetch the track row for that track from the track table. Obviously it makes much more sense to simply do a JOIN , but this code is more to show nested cursor looping than do anything particularly useful.

The tracks table is given below:

 

 

 

 

 

The matches table is given below:

 

 

 

 

 

 

 

The T-SQL code is given below in the form of a Stored Procedure.

 

 

USE [Data]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[Test_Nested_Cursors]  

AS
BEGIN
 declare @trackid as int

 declare @outer_cursor cursor
 declare @inner_cursor cursor

 declare @matchid as int
 declare @stationid as int
 declare @thistrackid as int
 declare @matchdate as datetime
 declare @matchtimestart as datetime
 declare @matchtimeend as datetime
 declare @streamfile as varchar(100)

 declare @fetch_outer_cursor int
 declare @fetch_inner_cursor int

 declare outer_cursor cursor static local for 
 SELECT 
  TOP 1000
      [TrackId]
  FROM [Data].[dbo].[Matches]
  
  /*loop through top level cursor*/
  open outer_cursor
  fetch next from outer_cursor into @trackid
  select @fetch_outer_cursor = @@FETCH_STATUS

  while @fetch_outer_cursor = 0
  begin
	print cast(@trackid  as varchar(20))

	 /*loop through second level cursor*/
	 set @inner_cursor  = cursor static local for 
	 select top 100 id, stationid, trackid, date, matchtimestart, matchtimeend, streamfile
		 from matches where trackid = @trackid order by MatchTimeStart
	 

	 open @inner_cursor
	 fetch next from @inner_cursor into @matchid, @stationid, @thistrackid, @matchdate, @matchtimestart, @matchtimeend, @streamfile
	 set @fetch_inner_cursor = @@FETCH_STATUS
	 while @fetch_inner_cursor = 0
	 begin

	  print cast(@matchid as varchar(20)) + ',' + cast(@stationid as varchar(20)) + ',' + cast(@thistrackid as varchar(20)) +
			',' + cast(@matchtimestart as varchar(30)) + '-' + cast(@matchtimeend as varchar(30))

	  fetch next from @inner_cursor into @matchid, @stationid, @thistrackid, @matchdate, @matchtimestart, @matchtimeend, @streamfile
	  set @fetch_inner_cursor = @@FETCH_STATUS
	 end
	 close @inner_cursor
	 deallocate @inner_cursor

 	 fetch next from outer_cursor into @trackid
	 set @fetch_outer_cursor = @@FETCH_STATUS


  end
 
  close match_cursor
  deallocate match_cursor
end

Some points to note in the script:

• Once each table is retrieved in a cursor we stored the @@FETCH_STATUS in a variable (@fetch_outer_cursor,@fetch_inner_cursor) .  This helps preserve the state of the cursors since we are using multiple cursors together. If we checked directly for @@FETCH_STATUS = 0 then it will return the status of the last cursor operation whichever it was. In this code, after the first iteration of the inner cursor, the outer cursor would throw an exception since the inner cursor would have overwritten the state of the outer cursor
• The inner cursor is closed and deallocated before the outer loop iterates back, to prevent memory leaks
• This nesting can be extended to 3 or 4 levels if required as long as the cursor state is preserved for each level.