Creating Recurive Stored Procedures in T-SQL.

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' Can not 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

AS

Declare

@cnt

int

;

set

@cnt

=

@count

-

1

;

Print

'

Executing Stored ProCedre:

'

CAST

(@count

AS

nvarchar

);

IF

@cnt

>

0

EXECUTE

Test_recursion @cnt;

-

Recursive Invocation.

Go

EXECUTE

Test_recursion

30

;

-

The Maximum Nesting Level of Stored Procedure IS 30.