x
login about faq Site discussion (meta-askssc)

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hi Friends, I have follwing stored procedure. i am getting Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). How can i solve this problem. ALTER PROCEDURE [dbo].[sp_Insert_Terminal_InRequest] (@p_i_TerminalCode int, @p_i_TerminalUserCode int, @p_i_ClientRequestCode int, @p_i_PacketCount int, @p_i_MsgClass int) AS DECLARE @l_i_QueueNum AS int= 0 BEGIN BEGIN try --INSERT INTO JagsQueueNum SELECT 1,@p_i_TerminalCode,'sp_Insert_Terminal_InRequest',0 DECLARE @debugqueuenum AS INTEGER INSERT INTO [q_debugRequest] (TerminalCode,TerminalUserCode,ClientRequestCode, MsgClass, PacketCount) SELECT @p_i_TerminalCode,@p_i_TerminalUserCode, @p_i_ClientRequestCode, @p_i_MsgClass, @p_i_PacketCount; SET @debugqueuenum=SCOPE_IDENTITY() DECLARE data_Cursor CURSOR STATIC FOR SELECT QueueNum FROM q_Request WHERE TerminalCode = @p_i_TerminalCode AND ClientRequestCode = @p_i_ClientRequestCode OPEN data_Cursor FETCH NEXT FROM data_Cursor INTO @l_i_QueueNum CLOSE data_Cursor DEALLOCATE data_Cursor IF (@@FETCH_STATUS 0) BEGIN UPDATE q_debugRequest SET IsUpdated=1 WHERE queueNum=@debugqueuenum BEGIN TRAN T1 --INSERT INTO JagsQueueNum SELECT SCOPE_IDENTITY(),@p_i_TerminalCode,'q_debugRequest' INSERT INTO [q_Request] (TerminalCode,TerminalUserCode,ClientRequestCode, MsgClass, PacketCount) SELECT @p_i_TerminalCode,@p_i_TerminalUserCode, @p_i_ClientRequestCode, @p_i_MsgClass, @p_i_PacketCount; --INSERT INTO JagsQueueNum SELECT SCOPE_IDENTITY(),@p_i_TerminalCode,'q_Request' COMMIT TRAN T1 END IF EXISTS(SELECT * FROM q_debugrequest WHERE queuenum=@debugqueuenum AND isupdated=0) BEGIN EXEC sp_Insert_Terminal_InRequest @p_i_TerminalCode,@p_i_TerminalUserCode,@p_i_ClientRequestCode,@p_i_PacketCount,@p_i_MsgClass END DELETE q_debugrequest WHERE queuenum=@debugqueuenum END TRY BEGIN CATCH EXEC usp_GetErrorInfo END catch END With Regards Jagannath pati
more ▼

asked Dec 01 '11 at 05:53 AM in Default

jagannath gravatar image

jagannath
1 1 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The maximum level of nested calling of stored procedure, trigger, view is 32. You are calling your procedure sp_Insert_Terminal_InRequest recursively. You should bring calling this nested calling out of the procedure like in a loop or CURSOR (you are using already).

more ▼

answered Dec 01 '11 at 06:30 AM

robbin gravatar image

robbin
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x340
x11

asked: Dec 01 '11 at 05:53 AM

Seen: 989 times

Last Updated: Dec 01 '11 at 05:53 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.