How to put this Function into the Procedure-CodePudding

I can now write a Function of the database directly to the tree structure data query as JSON, but I don't know how to Procedure

 
The CREATE FUNCTION TreeJson (@ Id INT, @ IsRoot INT) 
RETURNS NVARCHAR (MAX) 
The BEGIN 
DECLARE @ Json NVARCHAR (MAX)='{}' @ Root NVARCHAR (MAX) 
The SET @ Json=(SELECT *, JSON_QUERY (dbo. TreeJson (Id, 2)) AS Children FROM dbo. TreeTable WHERE Pid=@ Id FOR Json AUTO); 

IF (@ IsRoot=1) 
The BEGIN 
The SET @ Root=(select * FROM dbo. TreeTable WHERE Id=@ Id FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) 
The SET @ Json=JSON_MODIFY (@ Root, '$. Children, JSON_QUERY (@ Json)) 
The SET @ IsRoot=2 
END 

RETURN the @ Json 
END

When I try to use the stored procedure below, will appear the error Maximum stored procedure, function, trigger, or view nesting level is exceeded (32) limit.

 
CREATE PROCEDURE SelectTree 
@ Table NVARCHAR (128), 
@ Id BIGINT, 
@ IsRoot INT, 
@ OutJson NVARCHAR (MAX) OUTPUT 
AS 
The BEGIN 
DECLARE @ Json NVARCHAR (MAX)='{}' @ Children NVARCHAR (MAX), @ Root NVARCHAR (MAX), @ Sql NVARCHAR (MAX), @ TmpJson NVARCHAR (MAX) 
The EXEC SelectTree @ Table, @ Id, 2, @ TmpJson OUTPUT; 
SET @ Sql='select @ Json=(select *, JSON_QUERY (@ TmpJson) AS the Children FROM the WHERE Pid=' + @ Table + '@ Id FOR Json AUTO)'; 
The EXEC sp_executesql @ Sql, N '@ Json NVARCHAR (MAX) output, @ TmpJson NVARCHAR (MAX), @ Id BIGINT', @ Json output, @ TmpJson, @ Id; 


IF (@ IsRoot=1) 
The BEGIN 
SET @ Sql='Select @ Root=(Select * FROM' + @ Table + WHERE Id=@ Id FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) '; 
The EXEC sp_executesql @ Sql, N '@ Root NVARCHAR (MAX) output, @ Id BIGINT', @ Root output, @ Id; 
The SET @ Json=JSON_MODIFY (@ Root, '$. Children, JSON_QUERY (@ Json)); 
The SET @ IsRoot=2 
END 
The SET @ OutJson=@ Json; 
END

How do I change to?

CodePudding user response:

You don't have any conditions before recursive judgment? That is not dead cycle

CodePudding user response:

Thank you very much! I changed, become the following, but still can't return the correct data:

 
CREATE PROCEDURE SelectTree 
@ Table sysname, 
@ Id BIGINT, 
@ IsRoot INT, 

@ OutJson NVARCHAR (MAX) OUTPUT 
AS 
The BEGIN 
DECLARE @ Json NVARCHAR (MAX)='{}' @ Children NVARCHAR (MAX), @ Root NVARCHAR (MAX), @ Sql NVARCHAR (MAX), @ TmpJson NVARCHAR (MAX), @ CurId BIGINT 
DECLARE @ ChildTable TABLE ([Id] BIGINT) 


The SET @ Sql='Select Id from + quotename (@ Table) +' WHERE Pid='+ cast (@ Id as nvarchar); 
Insert into @ ChildTable exec (@ Sql); 



DECLARE the rs CURSOR LOCAL SCROLL FOR (select distinct Id from @ ChildTable) 
Open the rs 
The fetch next from rs into @ CurId; 
While @ @ FETCH_STATUS=0 
The begin 
The 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)'; 
The EXEC sp_executesql @ Sql, N '@ Json NVARCHAR (MAX) output, @ TmpJson NVARCHAR (MAX), @ Id BIGINT', @ Json output, @ TmpJson, @ CurId; 
The fetch next from rs into @ CurId; 
End 
Close the rs 
Deallocate rs 



IF (@ IsRoot=1) 
The BEGIN 
The SET @ Sql='Select @ Root=(Select * FROM' + quotename (@ Table) + 'WHERE Id=@ Id FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)'; 
The EXEC sp_executesql @ Sql, N '@ Root NVARCHAR (MAX) output, @ Id BIGINT', @ Root output, @ Id; 
The SET @ Json=JSON_MODIFY (@ Root, '$. Children, JSON_QUERY (@ Json)); 
The SET @ IsRoot=2 
END 
The SET @ OutJson=@ Json; 
END