PRB: Errors Creating Recursive Stored Procedures

  - Microsoft SQL Server version 4.2 for OS/2

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.