PRB: Errors Creating Recursive Stored Procedures

ID Number: Q64241

1.00 1.10 4.20

OS/2

Summary:

SYMPTOMS

The following SQL Server error (#2007) occurs when you try to

create a procedure, "add task," that is recursive on itself:

SQL error: cannot add rows to sysdepends for current stored

procedure because it depends on missing object "add task." 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 "Microsoft SQL Server Transact-SQL

User's Guide" 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.

Additional reference words: 1.00 1.10 4.20