JSON数组插入SQL表存储过程报错：无效对象名#SubJsonRequest

表结构回顾 #

• Doc表：Id(主键)、Name、Desc、RefName、IsActive
• Con表：Id(主键)、Field、Criter、DId(外键关联Doc.Id)、Conjunction、IsActive

错误原因分析 #

提示“Invalid object name '#SubJsonRequest'”，大概率是临时表#SubJsonRequest的作用域问题：要么没在循环外提前创建，要么在循环过程中被意外销毁。另外，用WHILE循环处理JSON数组本身就不是SQL Server的最优实践，推荐用基于集合的操作替代循环，效率更高且不易出错。

推荐方案：用OPENJSON批量插入（无循环） #

以下是优化后的SaveDoc存储过程，直接解析JSON数组批量插入两张表：

示例输入JSON #

{
  "Docs": [
    {
      "Name": "文档1",
      "Desc": "描述1",
      "RefName": "REF001",
      "IsActive": 1,
      "Conditions": [
        {
          "Field": "字段A",
          "Criter": "条件1",
          "Conjunction": "AND",
          "IsActive": 1
        },
        {
          "Field": "字段B",
          "Criter": "条件2",
          "Conjunction": "OR",
          "IsActive": 1
        }
      ]
    },
    {
      "Name": "文档2",
      "Desc": "描述2",
      "RefName": "REF002",
      "IsActive": 1,
      "Conditions": [
        {
          "Field": "字段C",
          "Criter": "条件3",
          "Conjunction": "AND",
          "IsActive": 1
        }
      ]
    }
  ]
}

优化后的存储过程 #

CREATE PROCEDURE SaveDoc
    @Json NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 临时表存储插入的Doc ID及对应的Conditions数组
    DECLARE @InsertedDocs TABLE (
        DocId INT,
        Conditions NVARCHAR(MAX)
    );

    -- 插入Doc表，同时捕获ID和关联的Conditions
    INSERT INTO Doc (Name, [Desc], RefName, IsActive)
    OUTPUT inserted.Id, JSON_QUERY(j.Conditions) INTO @InsertedDocs(DocId, Conditions)
    SELECT 
        j.Name,
        j.[Desc],
        j.RefName,
        j.IsActive
    FROM OPENJSON(@Json, '$.Docs')
    WITH (
        Name NVARCHAR(100),
        [Desc] NVARCHAR(MAX),
        RefName NVARCHAR(50),
        IsActive BIT,
        Conditions NVARCHAR(MAX) AS JSON -- 标记为JSON类型，保留数组结构
    ) j;

    -- 批量插入Con表，通过CROSS APPLY展开Conditions数组
    INSERT INTO Con (Field, Criter, DId, Conjunction, IsActive)
    SELECT
        c.Field,
        c.Criter,
        d.DocId,
        c.Conjunction,
        c.IsActive
    FROM @InsertedDocs d
    CROSS APPLY OPENJSON(d.Conditions)
    WITH (
        Field NVARCHAR(100),
        Criter NVARCHAR(MAX),
        Conjunction NVARCHAR(10),
        IsActive BIT
    ) c;
END

若坚持用循环处理（不推荐） #

如果一定要用WHILE循环，需确保临时表在循环外创建，避免作用域问题：

CREATE PROCEDURE SaveDoc
    @Json NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 提前解析所有Doc数据到临时表
    CREATE TABLE #Docs (
        RowId INT IDENTITY(1,1),
        Name NVARCHAR(100),
        [Desc] NVARCHAR(MAX),
        RefName NVARCHAR(50),
        IsActive BIT,
        Conditions NVARCHAR(MAX) AS JSON
    );

    INSERT INTO #Docs (Name, [Desc], RefName, IsActive, Conditions)
    SELECT 
        Name, [Desc], RefName, IsActive, Conditions
    FROM OPENJSON(@Json, '$.Docs')
    WITH (
        Name NVARCHAR(100),
        [Desc] NVARCHAR(MAX),
        RefName NVARCHAR(50),
        IsActive BIT,
        Conditions NVARCHAR(MAX) AS JSON
    );

    DECLARE @CurrentRow INT = 1;
    DECLARE @TotalRows INT = (SELECT COUNT(*) FROM #Docs);
    DECLARE @DocId INT;
    DECLARE @Name NVARCHAR(100);
    DECLARE @Desc NVARCHAR(MAX);
    DECLARE @RefName NVARCHAR(50);
    DECLARE @IsActive BIT;
    DECLARE @Conditions NVARCHAR(MAX);

    WHILE @CurrentRow <= @TotalRows
    BEGIN
        -- 获取当前行的Doc数据
        SELECT 
            @Name = Name,
            @Desc = [Desc],
            @RefName = RefName,
            @IsActive = IsActive,
            @Conditions = Conditions
        FROM #Docs WHERE RowId = @CurrentRow;

        -- 插入Doc表并获取自增ID
        INSERT INTO Doc (Name, [Desc], RefName, IsActive)
        VALUES (@Name, @Desc, @RefName, @IsActive);
        SET @DocId = SCOPE_IDENTITY();

        -- 插入关联的Conditions到Con表
        INSERT INTO Con (Field, Criter, DId, Conjunction, IsActive)
        SELECT
            Field, Criter, @DocId, Conjunction, IsActive
        FROM OPENJSON(@Conditions)
        WITH (
            Field NVARCHAR(100),
            Criter NVARCHAR(MAX),
            Conjunction NVARCHAR(10),
            IsActive BIT
        );

        SET @CurrentRow = @CurrentRow + 1;
    END

    DROP TABLE #Docs;
END

内容的提问来源于stack exchange，提问作者Dotnet