T-SQL Puzzler-爬行对象依赖项

闲看花开花落

此代码涉及一个递归存储过程调用和一种避免游标名称冲突的“不太好”的方法。最后，我不在乎它是否使用游标。只是在寻找最优雅的方法。我主要将其用作跟踪Stored
Proc层次结构的简单方法（无需购买产品）。我在“动态sql”中尝试了游标，但运气不佳。我想深入十个层次。
所需的输出：
sp_Master_Proc_Name
-sp_Child_Proc_1_Name
---- sp_Sub_Proc_1_Name
-sp_Child_Proc_2_Name
-sp_Child_Proc_3_Name
它不是很漂亮，但是这里是代码（它没有按预期工作）
CREATE PROCEDURE SP_GET_DEPENDENCIES
(
   @obj_name varchar(300),
   @level int
)
AS
DECLARE @sub_obj_name varchar(300)
IF @level = 1
   BEGIN
      PRINT @obj_name
   END
IF @level = 1
   BEGIN
      DECLARE the_cursor_1 CURSOR FOR
         SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
            INNER JOIN dbo.sysobjects b ON a.id = b.id
            INNER JOIN dbo.sysobjects c ON a.depid = c.id
            WHERE b.name = @obj_name
      OPEN the_cursor_1
      SET @level = @level + 1
      FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name
      WHILE @@FETCH_STATUS = 0
      BEGIN
         PRINT @sub_obj_name
         EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level
         FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name
      END
      CLOSE the_cursor_1
      DEALLOCATE the_cursor_1
   END
IF @level = 2
   BEGIN
      DECLARE the_cursor_2 CURSOR FOR
         SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
            INNER JOIN dbo.sysobjects b ON a.id = b.id
            INNER JOIN dbo.sysobjects c ON a.depid = c.id
            WHERE b.name = @obj_name
      OPEN the_cursor_2
      SET @level = @level + 1
      FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name
      WHILE @@FETCH_STATUS = 0
      BEGIN
         PRINT @sub_obj_name
         EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level
         FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name
      END
      CLOSE the_cursor_2
      DEALLOCATE the_cursor_2
   END
IF @level = 3
   BEGIN
      DECLARE the_cursor_3 CURSOR FOR
         SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
            INNER JOIN dbo.sysobjects b ON a.id = b.id
            INNER JOIN dbo.sysobjects c ON a.depid = c.id
            WHERE b.name = @obj_name
      OPEN the_cursor_3
      SET @level = @level + 1
      FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name
      WHILE @@FETCH_STATUS = 0
      BEGIN
         PRINT @sub_obj_name
         EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level
         FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name
      END
      CLOSE the_cursor_3
      DEALLOCATE the_cursor_3
   END

解决方案:


            对于ms sql服务器，您可以使用CURSOR LOCAL，然后游标位于sproc调用的本地，您的代码将变得更加简单：
CREATE PROCEDURE uspPrintDependencies
(
@obj_name varchar(300),
@level int
)
AS
SET NOCOUNT ON
DECLARE @sub_obj_name varchar(300)
if @level > 0 begin
PRINT Replicate(' ',@level) + @obj_name
end
else begin
PRINT @obj_name
end
DECLARE myCursor CURSOR LOCAL FOR
SELECT
      DISTINCT c.name
FROM dbo.sysdepends a
      INNER JOIN dbo.sysobjects b ON a.id = b.id
      INNER JOIN dbo.sysobjects c ON a.depid = c.id
WHERE b.name = @obj_name
OPEN myCursor
SET @level = @level + 1
FETCH NEXT FROM myCursor INTO @sub_obj_name
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC uspPrintDependencies @sub_obj_name, @level
FETCH NEXT FROM myCursor INTO @sub_obj_name
END
CLOSE myCursor
DEALLOCATE myCursor
GO

T-SQL Puzzler-爬行对象依赖项

闲看花开花落 LV1