How to execute two function according to a table result with a condition by using mssql?

Question

ParseValuesJson and insertToTable are user defined function.

The following is the result of the execution ( ParseValuesJson ) :

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

declare 
    @tempTable table (
        topKey nvarchar(4000)
        , [key] nvarchar(4000)
        , IsType bit
        , IsList bit
        , [value] nvarchar(4000))

--execute
insert @tempTable  
select * from GetValuesJson(@json,default)

topKey  Key         isType  isList  Value
--
Book    Type        0       0       Any Type
Book    Author      1       0       {"Name":"Annie", "Sex":"Female"}
Book    IssueDate   0       0       02-15-2019
Book    Chapter     1       1       [{"Section":"1.1", "Title":"Hello world."}, {"Section":"1.2", "Title":"Be happy."}]
Book    Sponsor     1       1       ["A","B","C"]

As title, If ignore what the function doing, how can I achieve the following purpose?

If IsType=1 , I want to call function ParseValuesJson; If IsType=0 , I want to call function insertToTable.

But I found that sql case can not use like that.

This sql query may execute recursively and call different functions accordingly at the same level.

It means that I can't parse all string (ParseValuesJson) first and then insert the result (insertToTable) to the table.

Is there any other way to achieve?

select 
    case IsType when 1 then
        ParseValuesJson('{"' + [key] + '":' + [value] + '}',IsList)
    else
        insertToTable(topKey,[key])
    end
from ParseValuesJson(@json,default)

Answer 1

Well, the easiest thing to do is to split it into two separate SELECTs.

select ParseValuesJson('{"' + [key] + '":' + [value] + '}',IsList)
from ParseValuesJson(@json,default)
where IsType = 1


select insertToTable(topKey,[key])
from ParseValuesJson(@json,default)
where IsType = 0

But I guess this approach won't help you since inside user defined functions you cannot use INSERT, UPDATE, DELETE statements -> i.e modify table data

So I guess that to parse the JSON you'd need to user recursive CTE to parse all values first and then to insert them into temp table at once.

Something like this:

;WITH ParsedJSON(topKey, [key], IsType, IsList, [value])
AS
(
SELECT topKey, [key], IsType, IsList, [value]
FROM ParseValuesJson('{"' + [key] + '":' + [value] + '}',IsList)

UNION ALL

SELECT topKey, [key], IsType, IsList, [value]
FROM ParsedJSON
WHERE IsType = 1
) 
insert @tempTable  
select * from ParsedJSON