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

对于ms sql server你可以使用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