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 nesting limit, an error
message is raised, the batch is aborted, and any open transaction in the
session is 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 to which a procedure is
nested, you use the system function @@NESTLEVEL (see Listing 1).
Listing 1. 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_
proc 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
The module 'main_proc' depends on the missing object 'sub_proc1'. The module
will still be created; however, it cannot run successfully until the object
exists.
The module 'main_proc' depends on the missing object 'sub_proc2'. The module
will still be created; however, it cannot run successfully until the object
exists.
The module 'sub_proc1' depends on the missing object 'sub_proc2'. The module
will still be created; however, it cannot run successfully until the object
exists.
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_proc 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 to which 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 never
exceeds two.
Any stored procedure that is
called from within another procedure should return a status code if an
error condition occurs. Depending on the severity of the error, failure
within a nested procedure does not always cause the calling procedure or
batch to be aborted. Checking the error condition from a nested
procedure allows 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.
When might you want a stored procedure to be recursive? One common
example is when you need to expand a tree relationship. Although a
common table expression (CTE) can be used to recursively expand a tree
relationship, internally it builds the entire tree before applying any
filters to display the tree, starting at a specific level. It is also
somewhat limited in how the tree is displayed (see Listing 2).
Listing 2. Using a Common Table Expression to Expand a Tree Relationship
WITH PartsTree (PartID, PartName, parentPartID, Level)
AS
(
-- Anchor member definition
SELECT PartID, PartName, ParentPartID,
0 AS Level
FROM Parts AS parent
where Parent.parentpartid is null
UNION ALL
-- Recursive member definition
SELECT child.PartID, child.PartName, child.ParentPArtID,
Level + 1
FROM Parts AS Child
INNER JOIN PartsTree AS parent
ON child.ParentPartID = parent.PArtID
)
-- Statement that executes the CTE
select * from PArtsTree
go
PartID PartName parentPartID Level
----------- ------------------------------ ------------ -----------
22 Car NULL 0
1 DriveTrain 22 1
23 Body 22 1
24 Frame 22 1
2 Engine 1 2
3 Transmission 1 2
4 Axle 1 2
12 Drive Shaft 1 2
9 Flywheel 3 3
10 Clutch 3 3
16 Gear Box 3 3
15 Reverse Gear 16 4
17 First Gear 16 4
18 Second Gear 16 4
19 Third Gear 16 4
20 Fourth Gear 16 4
5 Radiator 2 3
6 Intake Manifold 2 3
7 Exhaust Manifold 2 3
8 Carburetor 2 3
13 Piston 2 3
14 Crankshaft 2 3
21 Piston Rings 13 4
11 Float Valve 8 4
|
A
recursive procedure can provide a somewhat more elegant solution to
expanding a tree relationship from any level in the tree. This solution
also provides more control over formatting of the output. For example,
the procedure in Listing 3 formats the output so that the child parts are indented within the parent part.
Listing 3. Expanding a Tree Relationship by Using a Recursive Procedure
IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id('dbo')
AND name = N'SHOW_PARTS_LIST')
DROP PROCEDURE dbo.SHOW_PARTS_LIST
GO
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'
declare @car_partid int
select @car_partid = partid from Parts where PartName = 'Engine'
exec show_parts_list @partid = @car_partid
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
|
|
