Nested Stored Procedures

Stored procedures can call other stored procedures, and any of those procedures can call other procedures up to a maximum nesting level of 32 levels deep. If you exceed the 32-level limit, an error message will be raised, the batch will be aborted, and any open transaction in the session will be rolled back. The nesting level limit prevents a recursive procedure from calling itself repeatedly in an infinite loop until a stack overflow occurs. To check the depth that a procedure is nested, use the system function @@NESTLEVEL (see Listing 28.19).

Listing 28.19 Checking @@NESTLEVEL in Nested Stored Procedures

 create proc main_proc as print 'Nesting Level in main_proc before sub_proc1 = ' + str(@@NESTLEVEL, 1) exec sub_proc1 print 'Nesting Level in main_proce after sub_proc1 = ' + str(@@NESTLEVEL, 1) exec sub_proc2 print 'Nesting Level in main_proc after sub_proc2 = ' + str(@@NESTLEVEL, 1) return go create proc sub_proc1 as print 'Nesting Level in sub_proc1 before sub_proc2 = ' + str(@@NESTLEVEL, 1) exec sub_proc2 print 'Nesting Level in sub_proc1 after sub_proc2 = ' + str(@@NESTLEVEL, 1) return go create proc sub_proc2 as print 'Nesting Level in sub_proc2 = ' + str(@@NESTLEVEL, 1) return go print 'Nesting Level before main_proc = ' + str(@@NESTLEVEL, 1) exec main_proc print 'Nesting Level after main_proc = ' + str(@@NESTLEVEL, 1) go select @@NESTLEVEL exec main_proc select @@NESTLEVEL go Nesting Level before main_proc = 0 Nesting Level in main_proc before sub_proc1 = 1 Nesting Level in sub_proc1 before sub_proc2 = 2 Nesting Level in sub_proc2 = 3 Nesting Level in sub_proc1 after sub_proc2 = 2 Nesting Level in main_proce after sub_proc1 = 1 Nesting Level in sub_proc2 = 2 Nesting Level in main_proc after sub_proc2 = 1 Nesting Level after main_proc = 0 

Although a limit exists for the number of levels that procedures can be nested, the number of stored procedures that can be called from within a single procedure is limitless. The main level procedure can call potentially hundreds of other procedures. As long as the other procedures never invoke another procedure, the nesting level will never exceed two.

Any stored procedure that is called from within another procedure should always return a status code if an error condition occurs. Depending on the severity of the error, failure within a nested procedure will not always cause the calling procedure or batch to be aborted. Checking the error condition from a nested procedure will allow you to conditionally determine whether to continue processing.

Recursive Stored Procedures

A stored procedure can call itself up to the maximum nesting level of 32. This is referred to as recursion. Be aware that when you create a recursive procedure, it will generate the following warning message:

 Cannot add rows to sysdepends for the current stored procedure because it  depends on the missing object '  procname  '. The stored procedure will still be created. 

This is simply because it is trying to add the dependency to itself, which it cannot do because it doesn't exist yet. This does not affect the functionality of the stored procedure in any way; it will correctly resolve the reference to itself at runtime, and the warning can be ignored.

When might you want a stored procedure to be recursive? One common example is when you need to expand a tree relationship. Although a self-join can expand a tree relationship, it shows the entire tree relationship (see Listing 28.20). With this type of query, it is difficult to expand the tree at a specific level or to show the tree in a specific hierarchy order when the rows or ID values are not in a logical order in the table.

Listing 28.20 Using a Self-Join to Expand a Tree Relationship

 SELECT child.partid, child.partname, parent.partid, parent.partname    FROM PARTS child left outer join Parts parent    on child.parentpartid = parent.partid   order by parent.partid partid      partname                     partid          partname ----------- ---------------------------- --------------- -------- 22          Car                          NULL            NULL 2           Engine                       1               DriveTrain 3           Transmission                 1               DriveTrain 4           Axle                         1               DriveTrain 12          Drive Shaft                  1               DriveTrain 13          Piston                       2               Engine 14          Crankshaft                   2               Engine 5           Radiator                     2               Engine 6           Intake Manifold              2               Engine 7           Exhaust Manifold             2               Engine 8           Carburetor                   2               Engine 16          Gear Box                     3               Transmission 9           Flywheel                     3               Transmission 10          Clutch                       3               Transmission 11          Float Valve                  8               Carburetor 21          Piston Rings                 13              Piston 17          First Gear                   16              Gear Box 18          Second Gear                  16              Gear Box 19          Third Gear                   16              Gear Box 20          Fourth Gear                  16              Gear Box 15          Reverse Gear                 16              Gear Box 1           DriveTrain                   22              Car 23          Body                         22              Car 24          Frame                        22              Car 

A recursive procedure provides an elegant solution to expand a tree relationship from any level in the tree. This solution also lets you format the output so the child parts are indented within the parent part. An example is shown in Listing 28.21.

Listing 28.21 Expanding a Tree Relationship Using a Recursive Procedure

 CREATE PROC SHOW_PARTS_LIST @partid varchar(50) as set nocount on declare @treelevel int,         @partname varchar(50),         @childpartid int,         @parentpartid int select @treelevel = @@NESTLEVEL -- keep track of nesting level for indenting if @@nestlevel = 1  -- this is the top of the tree begin     select @partname = PArtName from Parts where Partid = @partid     print 'Expanded parts list for ' + @partname end if @@NESTLEVEL < 32  -- Make sure we don't exceed the maximum nesting level begin     -- set up cursor to find all child parts for the current part     declare c1 cursor local for         select PartId, PartName from Parts            where parentpartid = @partid     open c1     fetch c1 into @childpartid, @partname     while @@fetch_Status = 0     begin         -- use the current tree level to set the indenting when         --  we print out this record         print replicate('-', @treelevel * 3) + '> '                + @partname + ', Part Number: ' + ltrim(str(@childpartid))         -- Now, call the procedure again to find all the child parts         --  for the current part         exec show_parts_list @childpartid         fetch c1 into @childpartid, @partname     end     close c1     deallocate c1 end else begin     -- We are at maximum nesting level, print out message to indicate this     print 'Nesting level at 32. Cannot expand tree further.' end return go -- show the whole parts tree declare @car_partid int select @car_partid = partid from Parts where PartName = 'Car' exec show_parts_list @partid = @car_partid go Expanded parts list for Car ---> DriveTrain, Part Number: 1 ------> Engine, Part Number: 2 ---------> Radiator, Part Number: 5 ---------> Intake Manifold, Part Number: 6 ---------> Exhaust Manifold, Part Number: 7 ---------> Carburetor, Part Number: 8 ------------> Float Valve, Part Number: 11 ---------> Piston, Part Number: 13 ------------> Piston Rings, Part Number: 21 ---------> Crankshaft, Part Number: 14 ------> Transmission, Part Number: 3 ---------> Flywheel, Part Number: 9 ---------> Clutch, Part Number: 10 ---------> Gear Box, Part Number: 16 ------------> Reverse Gear, Part Number: 15 ------------> First Gear, Part Number: 17 ------------> Second Gear, Part Number: 18 ------------> Third Gear, Part Number: 19 ------------> Fourth Gear, Part Number: 20 ------> Axle, Part Number: 4 ------> Drive Shaft, Part Number: 12 ---> Body, Part Number: 23 ---> Frame, Part Number: 24 -- show the parts tree for 'Engine' exec show_parts_list @partid = 2 go Expanded parts list for Engine ---> Radiator, Part Number: 5 ---> Intake Manifold, Part Number: 6 ---> Exhaust Manifold, Part Number: 7 ---> Carburetor, Part Number: 8 ------> Float Valve, Part Number: 11 ---> Piston, Part Number: 13 ------> Piston Rings, Part Number: 21 ---> Crankshaft, Part Number: 14