Nested Stored Procedures [Resolved]

Posted by Chirag.patel2121 under Sql Server on 8/14/2010 | Points: 10 | Views : 3646 | Status : [Member] | Replies : 1
Is it possible nested stored procedure? if yes how many nested stored procedure possible?
Please also give examples and inform when and where it is used most?
thanks in advance
Chirag Patel
Jr.Software Developer

ckpatel


Responses

Posted by: PandianS on: 8/15/2010 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi

Maximum nested level of procedure, function, trigger or view is 32.

Step: 1
CREATE PROC USP_PROC1

AS
BEGIN
SET NOCOUNT ON
SELECT 'DotnetFunda' 'Procedure Name'
END
GO
Step: 2
CREATE PROC USP_PROC2

AS
BEGIN
SET NOCOUNT ON
EXEC USP_PROC1
EXEC USP_PROC2
END
GO
Step: 3
EXEC USP_PROC2
Result:
Msg 217, Level 16, State 1, Procedure USP_PROC2, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Conclusion: The nested level is 32

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Chirag.patel2121, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response