|
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
(comments are locked)
|
|
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).
(comments are locked)
|

