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.