Необходимо объединить динамический SQL, Open Query, JSON, динамические переменные и некоторые другие странности в один запрос

Question

Необходимо запустить динамический SQL для DB2 на MS SQL через OpenQuery, получить результаты обратно в JSON, а затем вернуть его как выходной параметр в хранимой процедуре

Я попытался использовать табличную переменную, как показывает пример кода, но я получаю эту ошибку:

Предложение FOR JSON не допускается в инструкции INSERT

Я также попытался обернуть запрос в CTE, но, учитывая изменения имени столбца JSON, я не могу использовать *, или я получаю эту ошибку:

Для столбца 1 таблицы tbl не было указано имя столбца.

Так что я в растерянности. Мне нужно запустить это и получить JSON в параметре Output, но, учитывая, что мне нужно смешать вызов DB2 через OpenQuery и динамический SQL, чтобы установить параметр, я не могу найти синтаксис, который работает.

create procedure uspTesting (
    @inAccountNumber nvarchar(20), 
    @outJSON nvarchar(max) output)
as
begin declare @result table (ResultJson nvarchar(max));

    declare @tsql nvarchar(4000) = '
    select name, age
    from  openquery(db2link,''
        select  name, 
                age 
        from    db2.account 
        where   accountnumber = ''''' + @inAccountNumber + ''''')'') tbl for json auto';

    insert into @result
    EXEC (@TSQL);

    select @outJSON = ResultJson from @result; End

Результаты, которые я ищу, - это строка JSON в выходном параметре @outJSON.

Answer 1

Примените FOR JSON после того, как вы получили данные, загрузите их во временную таблицу и затем используйте FOR JSON.

Без тестовых данных и т. Д. Вам, возможно, придется изменить это, но попробуйте что-то вроде:

CREATE PROCEDURE [uspTesting]
    (
        @inAccountNumber NVARCHAR(20)
      , @outJSON NVARCHAR(MAX) OUTPUT
    )
AS
    BEGIN
        DECLARE @result TABLE
            (
                [name] NVARCHAR(100) --whatever data type you need here
              , [age] NVARCHAR(100)
            );

        DECLARE @tsql NVARCHAR(4000) = '
    select name, age
    from  openquery(db2link,''
        select  name, 
                age 
        from    db2.account 
        where   accountnumber = ''' + @inAccountNumber + ''')';

        --Here we will just load a table variable with the data.
        INSERT INTO @result
        EXEC ( @tsql );

        --Then we will select from that table variable applying the JSON here.
        SET @outJSON = (
                           SELECT *
                           FROM   @result
                           FOR JSON AUTO
                       );

    END;