Can I use sql recursive in sql cursor?

Question

I'm writing a procedure which want to excecute the function recursively in cursor by using mssql.

The function ParseJson in the following code is refer to How to parse JSON string recursively with openjson

PS. Reference example is recursive version, but in my question it is parse step by step.

It's the first result of my function ParseJson

    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"]

The value of each column isTerminal is the condition, when isTerminal=0 then execute the function ParseJson; when isTerminal=1 then print something.

I'm create a procedure to excecute the function recursively in sql cursor. The function is create sucessful, but excecute fail.

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

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

Program CursorJson, [Batch Start Line 0] The cursor with the name 'myCursor' already exists.

Answer 1

The "quick fix" would be to specify that the cursor is local:

declare myCursor cursor local for

But I'd seriously re-examine whether you can achieve what you're trying to do by using a recursive CTE instead.