需要将动态SQL，Open Query，JSON，动态变量和其他一些奇怪的东西混合到一个查询中

Question

需要通过 OpenQuery 对 MS SQL 上的 DB2 运行动态 SQL，以 JSON 格式获取结果，然后将其作为存储过程中的输出参数返回

我尝试使用示例代码所示的表变量，但出现此错误：

FOR JSON 子句不允许在 INSERT 语句中使用

我也尝试将查询包装到 CTE 中，但鉴于 JSON 列名称更改，我无法使用 * 或者出现此错误：

没有为"tbl"的第 1 列指定列名。

所以我不知所措。我需要运行它并在输出参数中获取 JSON，但鉴于我必须通过 OpenQuery 和动态 SQL 混合调用 DB2 来设置参数，我找不到有效的语法。

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

我正在寻找的结果是输出参数 @outJSON 中的 JSON 字符串。

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;