我現在寫了一個 Function 可以直接把資料庫的樹形結構資料查詢為 JSON，但我不知道怎么轉為 Procedure
CREATE FUNCTION TreeJson(@Id INT, @IsRoot INT)
RETURNS NVARCHAR(MAX)
BEGIN
DECLARE @Json NVARCHAR(MAX) = '{}', @Root NVARCHAR(MAX)
SET @Json = (SELECT *, JSON_QUERY(dbo.TreeJson(Id, 2) ) AS Children FROM dbo.TreeTable WHERE Pid = @Id FOR JSON AUTO);
IF(@IsRoot = 1)
BEGIN
SET @Root = (select * FROM dbo.TreeTable WHERE Id = @Id FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)
SET @Json = JSON_MODIFY(@Root, '$.Children', JSON_QUERY(@Json))
SET @IsRoot = 2
END
RETURN @Json
END
當我嘗試用下面的存盤程序時，會出現這個錯誤 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
CREATE PROCEDURE SelectTree
@Table NVARCHAR(128),
@Id BIGINT,
@IsRoot INT,
@OutJson NVARCHAR(MAX) OUTPUT
AS
BEGIN
DECLARE @Json NVARCHAR(MAX) = '{}',@Children NVARCHAR(MAX), @Root NVARCHAR(MAX), @Sql NVARCHAR(MAX), @TmpJson NVARCHAR(MAX)
EXEC SelectTree @Table, @Id, 2, @TmpJson OUTPUT;
SET @Sql = 'select @Json = (SELECT *, JSON_QUERY(@TmpJson) AS Children FROM ' + @Table + ' WHERE Pid = @Id FOR JSON AUTO)';
EXEC sp_executesql @Sql, N'@Json NVARCHAR(MAX) output, @TmpJson NVARCHAR(MAX), @Id BIGINT', @Json output, @TmpJson, @Id;
IF(@IsRoot = 1)
BEGIN
SET @Sql = 'Select @Root = (select * FROM ' + @Table + ' WHERE Id = @Id FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)';
EXEC sp_executesql @Sql, N'@Root NVARCHAR(MAX) output, @Id BIGINT', @Root output, @Id;
SET @Json = JSON_MODIFY(@Root, '$.Children', JSON_QUERY(@Json));
SET @IsRoot = 2
END
SET @OutJson = @Json;
END
我要怎么改才可以呢？
uj5u.com熱心網友回復：
你在遞回前 沒有任何條件判斷嗎? 那不是死回圈了uj5u.com熱心網友回復：
謝謝！我改了一下，變成以下的，但還是無法回傳正確的資料：
CREATE PROCEDURE SelectTree
@Table sysname,
@Id BIGINT,
@IsRoot INT,
@OutJson NVARCHAR(MAX) OUTPUT
AS
BEGIN
DECLARE @Json NVARCHAR(MAX) = '{}',@Children NVARCHAR(MAX), @Root NVARCHAR(MAX), @Sql NVARCHAR(MAX), @TmpJson NVARCHAR(MAX), @CurId BIGINT
DECLARE @ChildTable TABLE([Id] BIGINT)
SET @Sql = 'Select Id from ' + quotename(@Table) + ' WHERE Pid = ' + cast(@Id as nvarchar);
Insert into @ChildTable exec(@Sql);
DECLARE rs CURSOR LOCAL SCROLL FOR (select distinct Id from @ChildTable)
open rs
fetch next from rs into @CurId;
while @@FETCH_STATUS = 0
begin
EXEC SelectTree @Table, @CurId, 2, @TmpJson OUTPUT;
SET @Sql = 'select @Json = (SELECT *, JSON_QUERY(@TmpJson) AS Children FROM ' + quotename(@Table) + ' WHERE Pid = @Id FOR JSON AUTO)';
EXEC sp_executesql @Sql, N'@Json NVARCHAR(MAX) output, @TmpJson NVARCHAR(MAX), @Id BIGINT', @Json output, @TmpJson, @CurId;
fetch next from rs into @CurId;
end
close rs
deallocate rs
IF(@IsRoot = 1)
BEGIN
SET @Sql = 'Select @Root = (select * FROM ' + quotename(@Table) + ' WHERE Id = @Id FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)';
EXEC sp_executesql @Sql, N'@Root NVARCHAR(MAX) output, @Id BIGINT', @Root output, @Id;
SET @Json = JSON_MODIFY(@Root, '$.Children', JSON_QUERY(@Json));
SET @IsRoot = 2
END
SET @OutJson = @Json;
END
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/73170.html
標籤：疑難問題
上一篇：求解