Creating recurive stored procedures in T-SQL.

最新推荐文章于 2016-01-26 17:14:00 发布

转载最新推荐文章于 2016-01-26 17:14:00 发布 · 51 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Laser_Lu/archive/2004/08/04/30177.html

文章标签：

#数据库

该博客为转载内容，转载自https://www.cnblogs.com/Laser_Lu/archive/2004/08/04/30177.html ，涉及数据库相关知识。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

In SQLServer, I tried to define a stored procedure to recursively invoke itself (See the following T-SQL statements). However, the maximum nesting level of recursion is 30 (Tested under SQL Server 2000). Once the nesting level is exceeded, an error will occur. Another thing I'd like to mention here is that, there would be a warning message prompted by SQL Server as 'Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'test_recursion'. The stored procedure will still be created.'.

create procedure test_recursion

@count int = 10

declare @cnt int;

set @cnt = @count - 1;

print 'executing stored procedre : ' + cast(@count as nvarchar);

if @cnt > 0 execute test_recursion @cnt; -- Recursive invocation.

execute test_recursion 30; -- The maximum nesting level of stored procedure is 30.

The output result of this procedure is as follows:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'test_recursion'. The stored procedure will still be created.
executing stored procedre : 30
executing stored procedre : 29
executing stored procedre : 28
executing stored procedre : 27
executing stored procedre : 26
executing stored procedre : 25
executing stored procedre : 24
executing stored procedre : 23
executing stored procedre : 22
executing stored procedre : 21
executing stored procedre : 20
executing stored procedre : 19
executing stored procedre : 18
executing stored procedre : 17
executing stored procedre : 16
executing stored procedre : 15
executing stored procedre : 14
executing stored procedre : 13
executing stored procedre : 12
executing stored procedre : 11
executing stored procedre : 10
executing stored procedre : 9
executing stored procedre : 8
executing stored procedre : 7
executing stored procedre : 6
executing stored procedre : 5
executing stored procedre : 4
executing stored procedre : 3
executing stored procedre : 2
executing stored procedre : 1

转载于:https://www.cnblogs.com/Laser_Lu/archive/2004/08/04/30177.html