sql-server - 如何将 SQL SERVER 函数转换为 PROCEDURE
问题描述
我有一个数据表如下:
+----+--------------+---------------------------+
| Id | Pid | Title |
+----+--------------+---------------------------+
| 1 | 0 | System |
| 2 | 1 | Setting |
| 3 | 1 | DataSource |
| 4 | 2 | Models |
| 5 | 4 | Users |
| 7 | 4 | Factory |
+----+--------------+---------------------------+
我完成了一个从数据表生成分层 json 的函数,如下所示,但我不知道如何将其转换为过程以使用动态表名:
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
我尝试转换为如下程序,但我无法获得正确的数据:
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
解决方案
推荐阅读
- python - MySql插入速度太慢
- python - 在 Windows(selenium、python)上使用 VPN 时 Chromedriver 无法启动
- php - 日期映射器不接受发送的数据,尽管它似乎是可接受的格式
- string - 批处理脚本 - 用从变量接收的另一个字符串替换字符串
- php - 如何将 cloudways CDN 集成到我的 Codeigniter 项目中
- r - R - 如何更改单个条形图的堆积条形图顺序?
- python - python减去键不匹配的字典
- excel - 如何使用电源查询来抓取 CME。?
- javascript - reject() 和 return 不会终止执行
- python - 如何避免分页Flask中随机显示的元素?