{"id":2075,"date":"2015-01-03T11:44:10","date_gmt":"2015-01-03T11:44:10","guid":{"rendered":"http:\/\/truelogic.org\/wordpress\/?p=2075"},"modified":"2015-01-03T11:44:10","modified_gmt":"2015-01-03T11:44:10","slug":"nested-cursors-in-t-sql","status":"publish","type":"post","link":"https:\/\/truelogic.org\/wordpress\/2015\/01\/03\/nested-cursors-in-t-sql\/","title":{"rendered":"Nested Cursors in T-SQL"},"content":{"rendered":"            <script type=\"text\/javascript\" src=\"https:\/\/truelogic.org\/wordpress\/wp-content\/plugins\/wordpress-code-snippet\/scripts\/shBrushSql.js\"><\/script>\n<p>We have two sample tables\u00a0 :<\/p>\n<ul>\n<li><strong>tracks<\/strong> &#8211; which store audio track info<\/li>\n<li><strong>matches<\/strong> &#8211; table which stores tracks matched in external data<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>The tracks table is given below:<\/p>\n<p><a href=\"https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/tracktable.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-medium wp-image-2076\" src=\"https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/tracktable-620x118.png\" alt=\"tracktable\" width=\"620\" height=\"118\" srcset=\"https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/tracktable-620x118.png 620w, https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/tracktable-300x57.png 300w, https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/tracktable.png 900w\" sizes=\"auto, (max-width: 620px) 100vw, 620px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>The matches table is given below:<\/p>\n<p><a href=\"https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/matchtable.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-medium wp-image-2077\" src=\"https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/matchtable-620x158.png\" alt=\"matchtable\" width=\"620\" height=\"158\" srcset=\"https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/matchtable-620x158.png 620w, https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/matchtable-300x76.png 300w, https:\/\/truelogic.org\/wordpress\/wp-content\/uploads\/2015\/01\/matchtable.png 797w\" sizes=\"auto, (max-width: 620px) 100vw, 620px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>The T-SQL code is given below in the form of a Stored Procedure.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><pre class=\"brush: sql\">USE [Data]\r\nGO\r\n\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nalter PROCEDURE [dbo].[Test_Nested_Cursors]  \r\n\r\nAS\r\nBEGIN\r\n declare @trackid as int\r\n\r\n declare @outer_cursor cursor\r\n declare @inner_cursor cursor\r\n\r\n declare @matchid as int\r\n declare @stationid as int\r\n declare @thistrackid as int\r\n declare @matchdate as datetime\r\n declare @matchtimestart as datetime\r\n declare @matchtimeend as datetime\r\n declare @streamfile as varchar(100)\r\n\r\n declare @fetch_outer_cursor int\r\n declare @fetch_inner_cursor int\r\n\r\n declare outer_cursor cursor static local for \r\n SELECT \r\n  TOP 1000\r\n      [TrackId]\r\n  FROM [Data].[dbo].[Matches]\r\n  \r\n  \/*loop through top level cursor*\/\r\n  open outer_cursor\r\n  fetch next from outer_cursor into @trackid\r\n  select @fetch_outer_cursor = @@FETCH_STATUS\r\n\r\n  while @fetch_outer_cursor = 0\r\n  begin\r\n\tprint cast(@trackid  as varchar(20))\r\n\r\n\t \/*loop through second level cursor*\/\r\n\t set @inner_cursor  = cursor static local for \r\n\t select top 100 id, stationid, trackid, date, matchtimestart, matchtimeend, streamfile\r\n\t\t from matches where trackid = @trackid order by MatchTimeStart\r\n\t \r\n\r\n\t open @inner_cursor\r\n\t fetch next from @inner_cursor into @matchid, @stationid, @thistrackid, @matchdate, @matchtimestart, @matchtimeend, @streamfile\r\n\t set @fetch_inner_cursor = @@FETCH_STATUS\r\n\t while @fetch_inner_cursor = 0\r\n\t begin\r\n\r\n\t  print cast(@matchid as varchar(20)) + &#039;,&#039; + cast(@stationid as varchar(20)) + &#039;,&#039; + cast(@thistrackid as varchar(20)) +\r\n\t\t\t&#039;,&#039; + cast(@matchtimestart as varchar(30)) + &#039;-&#039; + cast(@matchtimeend as varchar(30))\r\n\r\n\t  fetch next from @inner_cursor into @matchid, @stationid, @thistrackid, @matchdate, @matchtimestart, @matchtimeend, @streamfile\r\n\t  set @fetch_inner_cursor = @@FETCH_STATUS\r\n\t end\r\n\t close @inner_cursor\r\n\t deallocate @inner_cursor\r\n\r\n \t fetch next from outer_cursor into @trackid\r\n\t set @fetch_outer_cursor = @@FETCH_STATUS\r\n\r\n\r\n  end\r\n \r\n  close match_cursor\r\n  deallocate match_cursor\r\nend\r\n<\/pre><\/p>\n<p>Some points to note in the script:<\/p>\n<ul>\n<li>Once each table is retrieved in a cursor we stored the @@FETCH_STATUS in a variable (@fetch_outer_cursor,@fetch_inner_cursor) .\u00a0 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\n<div id=\"stcpDiv\"><\/div>\n<\/li>\n<li>The inner cursor is closed and deallocated before the outer loop iterates back, to prevent memory leaks<\/li>\n<li>This nesting can be extended to 3 or 4 levels if required as long as the cursor state is preserved for each level.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>We have two sample tables\u00a0 : tracks &#8211; which store audio track info matches &#8211; table which stores tracks matched in external data As you <a class=\"mh-excerpt-more\" href=\"https:\/\/truelogic.org\/wordpress\/2015\/01\/03\/nested-cursors-in-t-sql\/\" title=\"Nested Cursors in T-SQL\">[&#8230;]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":2087,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-2075","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver"],"_links":{"self":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/2075","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/comments?post=2075"}],"version-history":[{"count":9,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/2075\/revisions"}],"predecessor-version":[{"id":2086,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/2075\/revisions\/2086"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media\/2087"}],"wp:attachment":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media?parent=2075"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/categories?post=2075"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/tags?post=2075"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}