title: Can I use sql recursive in sql cursor?
body: <p>I'm writing a procedure which want to excecute the function recursively in  cursor by using mssql.</p>
  
  <p>The function <code>ParseJson</code> in the following code is refer to  <a href="https://stackoverflow.com/questions/54704149/how-to-parse-json-string-recursively-with-openjson">How to parse JSON string recursively with openjson</a></p>
  
  <p>PS. Reference example is recursive version, but in my question it is parse step by step.</p>
  
  <p>It's the first result of my function <code>ParseJson</code> </p>
  
  <pre><code>    topKey     Key         isTerminal     Value
      Book       IssueDate   1              02-15-2019
      Book       Detail      0              { "Type":"Any Type", "Author":{ "Name":"Annie" , "Sex":"Female"}
      Book       Chapter     0              [{ "Section":"1.1", "Title":"Hello world." }, { "Section":"1.2", "Title":"Be happy." }]
      Book       Sponsor     0              ["A","B","C"]
  </code></pre>
  
  <p>The value of each column <code>isTerminal</code> is the condition, 
  when <code>isTerminal=0</code> then execute the function <code>ParseJson</code>;
  when <code>isTerminal=1</code> then print something.</p>
  
  <p>I'm create a procedure to excecute the function recursively in sql cursor.
  The function is create sucessful, but excecute fail.</p>
  
  <pre><code>create procedure CursorJson
      @json nvarchar(max)
      , @Type nvarchar(max) 
      , @isArray bit = 0
  as
  begin
  
      set nocount on
  
      declare 
          @TopKey nvarchar(4000)
          , @Key nvarchar(4000)
          , @IsType bit
          , @IsList bit
          , @isTerminal bit
          , @Value nvarchar(4000)
  
      --defind
      declare myCursor cursor for
  
      --dataset
      select * from ParseJson(@json, @Type, @isArray) 
  
      --open
      open myCursor
  
      --run
      fetch next from myCursor into 
              @TopKey nvarchar(4000)
              , @Key nvarchar(4000)
              , @IsType bit
              , @IsList bit
              , @isTerminal bit
              , @Value nvarchar(4000)
  
      while(@@fetch_status = 0)
      begin
          if @isTerminal = 0 
          begin
              set @json = '{"' + @Key + '":' + @Value + '}'
              exec CursorJson @json, @Key, @isList 
          end
          else
          begin
              print 'insert...'
          end
  
          fetch next from myCursor into 
                  @TopKey nvarchar(4000)
                  , @Key nvarchar(4000)
                  , @IsType bit
                  , @IsList bit
                  , @isTerminal bit
                  , @Value nvarchar(4000)
      end
  
      --close and deallocate
      close myCursor 
      deallocate myCursor
  
      return
  end
  </code></pre>
  
  <pre><code>declare @Type nvarchar(max)=N'Book'
  declare 
      @json nvarchar(max)=N'{
      "Book":{
          "IssueDate":"02-15-2019"
          , "Detail":{
              "Type":"Any Type"
              , "Author":{
                  "Name":"Annie"
                  , "Sex":"Female"
              }
          }
          , "Chapter":[
              {
                  "Section":"1.1"
                  , "Title":"Hello world."
              }
              ,
              {
                  "Section":"1.2"
                  , "Title":"Be happy."
              }       
          ]
          , "Sponsor":["A","B","C"]
      }
  }'  
  
  --exec
  exec CursorJson @json, @Type, 0
  </code></pre>
  
  <blockquote>
    <p>Program CursorJson, [Batch Start Line 0] The cursor with the name 'myCursor' already exists.</p>
  </blockquote>