Microsoft KB Archive/64241

= PRB: Errors Creating Recursive Stored Procedures =

Article ID: 64241

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q64241



SYMPTOMS
The following SQL Server error (#2007) occurs when you try to create a procedure, &quot;add task,&quot; that is recursive on itself:

SQL error: cannot add rows to sysdepends for current stored procedure because it depends on missing object &quot;add task.&quot; The stored procedure will still be created.



CAUSE
This error is produced when SQL Server attempts to normalize your procedure to produce a query tree for future execution of your SQL statements. Before your stored procedure is created, it does not understand any reference to itself.

Although stored procedures are reusable, they are not reentrant; therefore, there is no way to call the procedure from within itself. The error that you are encountering is due to the stored procedure rule on page 292 of the &quot;Microsoft SQL Server Transact-SQL User's Guide&quot; for version 4.2, which states the following:

You can reference an object you've created in the same procedure as long as it is created before it is referenced.



WORKAROUND
If you need a single level of recursion only, this can be simulated by creating a separate procedure that has the same functionality. After it has been created, your first stored procedure can be modified to execute the second procedure when necessary.

Keywords: kbother KB64241

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.