如何将SQL Server层级JSON生成函数转换为支持动态表名的存储过程

Let's break down what's going wrong with your current stored procedure and fix it step by step. The core issue is that your loop overwrites the JSON variable instead of building a proper array of child nodes, and the recursive result handling isn't aggregating child data correctly. Here's the corrected solution, plus explanations of the key fixes:

Corrected Stored Procedure #

CREATE PROCEDURE SelectTree 
    @Table sysname, 
    @Id BIGINT, 
    @IsRoot INT, 
    @OutJson NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ChildJson NVARCHAR(MAX);
    DECLARE @ChildResults TABLE (JsonData NVARCHAR(MAX));
    DECLARE @Root NVARCHAR(MAX);
    DECLARE @Sql NVARCHAR(MAX);
    DECLARE @CurId BIGINT;
    DECLARE @ChildrenArray NVARCHAR(MAX);

    -- 1. Fetch all child IDs for the current node (dynamic table safe)
    DECLARE @ChildIds TABLE (Id BIGINT);
    SET @Sql = N'SELECT Id FROM ' + QUOTENAME(@Table) + N' WHERE Pid = @IdParam';
    INSERT INTO @ChildIds
    EXEC sp_executesql @Sql, N'@IdParam BIGINT', @IdParam = @Id;

    -- 2. Recursively get JSON for each child and collect results
    DECLARE ChildCursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT Id FROM @ChildIds;

    OPEN ChildCursor;
    FETCH NEXT FROM ChildCursor INTO @CurId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC SelectTree @Table, @CurId, 2, @ChildJson OUTPUT;
        INSERT INTO @ChildResults (JsonData) VALUES (@ChildJson);
        FETCH NEXT FROM ChildCursor INTO @CurId;
    END

    CLOSE ChildCursor;
    DEALLOCATE ChildCursor;

    -- 3. Convert collected child results into a valid JSON array
    SET @Sql = N'SELECT @ChildrenArray = (SELECT JsonData AS [*] FROM @ChildResults FOR JSON AUTO)';
    EXEC sp_executesql @Sql, 
        N'@ChildResults TABLE(JsonData NVARCHAR(MAX)), @ChildrenArray NVARCHAR(MAX) OUTPUT', 
        @ChildResults = @ChildResults, 
        @ChildrenArray = @ChildrenArray OUTPUT;

    SET @OutJson = ISNULL(@ChildrenArray, '[]');

    -- 4. Handle root node: attach children to the base node JSON
    IF @IsRoot = 1
    BEGIN
        SET @Sql = N'SELECT @Root = (SELECT * FROM ' + QUOTENAME(@Table) + N' WHERE Id = @IdParam FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)';
        EXEC sp_executesql @Sql, N'@Root NVARCHAR(MAX) OUTPUT, @IdParam BIGINT', @Root = @Root OUTPUT, @IdParam = @Id;
        
        SET @OutJson = JSON_MODIFY(@Root, '$.Children', JSON_QUERY(@OutJson));
    END
END

Key Fixes & Explanations #

• Collecting Child Results: Instead of overwriting @Json in each loop, we use a table variable @ChildResults to store every child's full recursive JSON output. This ensures no child data gets lost.
• Building Valid JSON Arrays: We convert the collected child JSON strings into a single JSON array using FOR JSON AUTO. This gives the proper nested Children structure instead of a single object.
• Dynamic SQL Safety: We use QUOTENAME(@Table) to prevent SQL injection when referencing dynamic table names, and always use parameterized queries with sp_executesql instead of concatenating values directly into SQL strings.
• Root Node Handling: For the root node, we first fetch the base node JSON, then use JSON_MODIFY with JSON_QUERY to attach the children array as a nested structure (instead of escaped plain text).

Testing the Procedure #

You can test it with your sample table using this code:

DECLARE @Result NVARCHAR(MAX);
EXEC SelectTree 'TreeTable', 1, 1, @Result OUTPUT;
PRINT @Result;

This will output the correct hierarchical JSON matching your original function's output:

{
  "Id": 1,
  "Pid": 0,
  "Title": "System",
  "Children": [
    {
      "Id": 2,
      "Pid": 1,
      "Title": "Setting",
      "Children": [
        {
          "Id": 4,
          "Pid": 2,
          "Title": "Models",
          "Children": [
            {"Id":5,"Pid":4,"Title":"Users"},
            {"Id":7,"Pid":4,"Title":"Factory"}
          ]
        }
      ]
    },
    {
      "Id": 3,
      "Pid": 1,
      "Title": "DataSource"
    }
  ]
}

Additional Notes #

• Ensure your target table always has Id and Pid columns (the logic relies on these for hierarchy). If your tables use different column names, add parameters for IdColumn and PidColumn to make the procedure more flexible.
• The FAST_FORWARD cursor option is used for better performance since we're only reading forward through child IDs.

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