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
