asked
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
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).
