x

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, 2011 at 05:53 AM in Default

jagannath gravatar image

jagannath
1 2 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, 2011 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x412
x11

asked: Dec 01, 2011 at 05:53 AM

Seen: 1627 times

Last Updated: Dec 01, 2011 at 05:53 AM