March 9, 2016 at 6:22 am
I have a SSRS report when generated, getting the error
Server: Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view
nesting level exceeded (limit 32).
I have the following stored proc used for generating the report.
CREATE PROCEDURE [dbo].usp_StatusHistory
@specimensCodes NVARCHAR (4000)
AS
-- Declare temp tables
CREATE TABLE #ItemHistory
(
storingEventSqlId SMALLINT,
storingEventIncId INT ,
containerSqlId SMALLINT,
containerIncId INT ,
storingEventModifiedOn DATETIME,
storingEventModifiedBySqlId SMALLINT,
storingEventModifiedByIncId SMALLINT
);
CREATE TABLE #Result
(
specimenCode NVARCHAR (25) NOT NULL,
clientSampleCode NVARCHAR (50) ,
internalSampleCode NVARCHAR (50) ,
dateIn DATETIME ,
operatorName NVARCHAR (120),
location NVARCHAR (MAX) NOT NULL
);
-- Declare variables
DECLARE @debug AS INT;
DECLARE @specimenCode AS NVARCHAR (25);
DECLARE @samplePartnerCode AS NVARCHAR (25);
DECLARE @internalSampleCode AS NVARCHAR (50);
DECLARE @clientSampleCode AS NVARCHAR (50);
DECLARE @specimensCodesCursor AS CURSOR;
DECLARE @samplePartnerKeyCursor AS CURSOR;
DECLARE @samplePartnerSqlId AS SMALLINT;
DECLARE @samplePartnerIncId AS INT;
DECLARE @itemHistoryCursor AS CURSOR;
DECLARE @modifiedOn AS DATETIME;
DECLARE @typeOfEventName AS NVARCHAR (50);
DECLARE @parentContainerName AS NVARCHAR (50);
DECLARE @operatorName AS NVARCHAR (120);
SET @debug = 0;
SET @specimensCodesCursor = CURSOR
FOR SELECT *
FROM dbo.eufn_e5_eLIMS_SE_Parse_Delimited_List (@specimensCodes, ',');
SET @itemHistoryCursor = CURSOR
FOR SELECT IH.storingEventModifiedOn,
O.OperatorName
FROM #ItemHistory AS IH
INNER JOIN
Operators AS O
ON O.OperatorSqlId = IH.storingEventModifiedBySqlId
AND O.OperatorIncId = IH.storingEventModifiedByIncId
AND O.isDeleted = 0
ORDER BY IH.storingEventModifiedOn ASC;
-- Code
IF @debug = 1
SELECT 'START eusp_e5_eSM_SE_GetHistoryForSpecimen',
@specimensCodes AS specimensCodes;
OPEN @specimensCodesCursor;
FETCH NEXT FROM @specimensCodesCursor INTO @specimenCode;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @debug = 1
SELECT @specimenCode AS specimenCode;
SET @specimenCode = '%' + @specimenCode + '%';
SET @samplePartnerKeyCursor = CURSOR
FOR SELECT samplePartnerCode,
clientSampleCode,
internalSampleCode,
samplePartnerSqlId,
samplePartnerIncId
FROM SamplesPartners
WHERE samplePartnerCode LIKE @specimenCode
OR clientSampleCode LIKE @specimenCode
OR internalSampleCode LIKE @specimenCode;
OPEN @samplePartnerKeyCursor;
FETCH @samplePartnerKeyCursor INTO @samplePartnerCode, @clientSampleCode, @internalSampleCode, @samplePartnerSqlId, @samplePartnerIncId;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @debug = 1
SELECT @samplePartnerSqlId AS samplePartnerSqlId,
@samplePartnerIncId AS samplePartnerIncId;
EXECUTE [dbo].eusp_e5_eSM_SE_GetHistoryForItem NULL, NULL, @samplePartnerSqlId, @samplePartnerIncId, 2, 293;
IF @debug = 1
SELECT 'RESULT #ItemHistory',
*
FROM #ItemHistory;
OPEN @itemHistoryCursor;
FETCH NEXT FROM @itemHistoryCursor INTO @modifiedOn, @operatorName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @debug = 1
SELECT 'RESULT #Parents',
*
FROM #Parents;
DECLARE @location AS NVARCHAR (MAX);
SET @location = [dbo].eufn_e5_eSM_SE_GetItemLocation(@modifiedOn, @samplePartnerSqlId, @samplePartnerIncId, 2, 293);
IF @debug = 1
SELECT @location AS location;
INSERT INTO #Result
SELECT @samplePartnerCode,
@clientSampleCode,
@internalSampleCode,
@modifiedOn,
@operatorName,
@location;
FETCH NEXT FROM @itemHistoryCursor INTO @modifiedOn, @operatorName;
END
CLOSE @itemHistoryCursor;
DELETE #ItemHistory;
FETCH @samplePartnerKeyCursor INTO @samplePartnerCode, @clientSampleCode, @internalSampleCode, @samplePartnerSqlId, @samplePartnerIncId;
END
CLOSE @samplePartnerKeyCursor;
FETCH NEXT FROM @specimensCodesCursor INTO @specimenCode;
END
CLOSE @specimensCodesCursor;
DROP TABLE #ItemHistory;
SELECT *
FROM #Result
ORDER BY internalSampleCode ASC, clientSampleCode ASC, dateIn ASC;
Please suggest how to rewrite the query to avoid this error?
March 9, 2016 at 6:30 am
You failed to give us ALL the code in your sproc, most especially those things that drive the cursor(s). First thing I would say is put the output of those into temp tables and drive your cursor from that.
Next is you are shredding delimited lists into a table structure. You ARE using Delimited8KSplit from here on SSC.com, right?!? 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 9, 2016 at 8:00 am
there's nothing in your procedure that requires a cursor, let alone a cursor/while/cursor triumvirate of looping constructs.
In the end, you populate a #Results table, and return the results.
while the rewrite to make it completely set based is not trivial, it's not horribly difficult either.
Lowell
March 10, 2016 at 12:22 am
I have posted the complete code for the stored proc here.
I need a alternate way that code which cause the error can be avoided.
I have not developed this code and I am seeing this code first time.
Thats why I caanot comment on the cursors used.
In my local server, this is working fine for the specimencodes input parameter passed.
Only in Production server, this is failing.
Thanks in advance for the help.
March 10, 2016 at 2:05 am
Junglee_George (3/10/2016)
I have posted the complete code for the stored proc here.I need a alternate way that code which cause the error can be avoided.
I have not developed this code and I am seeing this code first time.
Thats why I caanot comment on the cursors used.
In my local server, this is working fine for the specimencodes input parameter passed.
Only in Production server, this is failing.
Thanks in advance for the help.
Please post the code for these two objects:
[dbo].eusp_e5_eSM_SE_GetHistoryForItem
dbo.eufn_e5_eLIMS_SE_Parse_Delimited_List
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 10, 2016 at 2:51 am
I am posting the required code.
ALTER FUNCTION [dbo].[eufn_e5_eLIMS_SE_Parse_Delimited_List](
@listString nvarchar(4000),
@delimiter nvarchar(1)
)
RETURNS @listTable Table( listItem nvarchar(50) )
AS
BEGIN
DECLARE @pos int
DECLARE @itemString nvarchar(50)
SET @listString = LTRIM(RTRIM(@listString))+ @delimiter-- add trailing comma
SET @Pos = CHARINDEX(@delimiter, @listString, 1)-- first delimiter
IF REPLACE(@listString, @delimiter, '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @itemString = LTRIM(RTRIM(LEFT(@listString, @Pos - 1)))
IF @itemString <> ''
BEGIN
INSERT INTO @listTable (listItem) VALUES (@itemString)
END
SET @listString = RIGHT( @listString, LEN(@listString) - @Pos)
SET @Pos = CHARINDEX(@delimiter, @listString, 1)
END
END
RETURN
END
ALTER PROCEDURE [dbo].[eusp_e5_eSM_SE_GetHistoryForItem]
@periodStart DATETIME,
@periodEnd DATETIME,
@itemSqlId SMALLINT,
@itemIncId INT,
@itemMetaTableSqlId SMALLINT,
@itemMetaTableIncId INT
AS
-- Declare temp tables
CREATE TABLE #StoringEvents
(
storingEventSqlId SMALLINT,
storingEventIncId INT,
containerSqlId SMALLINT,
containerIncId INT,
storingEventModifiedOn DATETIME,
storingEventModifiedBySqlId SMALLINT,
storingEventModifiedByIncId SMALLINT,
)
-- Declare variables
DECLARE @storingEventsCursor AS CURSOR
DECLARE @parentsCursor AS CURSOR
DECLARE @containerSqlId AS SMALLINT
DECLARE @containerIncId AS INT
DECLARE @storingEventModifiedOn AS DATETIME
DECLARE @containerPeriodStart DATETIME
DECLARE @containerPeriodEnd DATETIME
DECLARE @parentContainerSqlId SMALLINT
DECLARE @parentContainerIncId INT
DECLARE @debug INT
DECLARE @nextStoringEventCursor CURSOR
SET @debug = 0
SET @storingEventsCursor = CURSOR FOR
SELECTcontainerSqlId,
containerIncId,
storingEventModifiedOn
FROM #StoringEvents
-- Code
IF @debug = 1
SELECT 'START eusp_e5_eSM_SE_GetHistoryForItem', @periodStart AS periodStart, @periodEnd AS periodEnd, @itemSqlId AS itemSqlId, @itemIncId AS itemIncId, @itemMetaTableSqlId AS itemMetaTableSqlId, @itemMetaTableIncId AS itemMetaTableIncId
INSERT INTO #StoringEvents
SELECTstoringEventSqlId,
storingEventIncId,
containerSqlId,
containerIncId,
storingEventModifiedOn,
storingEventModifiedBySqlId,
storingEventModifiedByIncId
FROM StoringsEvents
WHEREitemSqlId = @itemSqlId
AND itemIncId = @itemIncId
AND itemMetaTableSqlId = @itemMetaTableSqlId
AND itemMetaTableIncId = @itemMetaTableIncId
AND typeOfEventSqlId = 117
ANDtypeOfEventIncId = 1
AND (@periodStart IS NULL
OR(storingEventModifiedOn >= @periodStart) )
AND (@periodEnd IS NULL
OR(storingEventModifiedOn <= @periodEnd) )
AND isDeleted = 0
IF @debug = 1
SELECT '#StoringEvents', * FROM #StoringEvents
INSERT INTO #ItemHistory
SELECT * FROM #StoringEvents SE
WHERE (SELECT COUNT(*)
FROM #ItemHistory IH
WHERESE.storingEventSqlId = IH.storingEventSqlId
AND SE.storingEventIncId = IH.storingEventIncId) = 0 -- Insert only once
OPEN @storingEventsCursor
FETCH NEXT FROM @storingEventsCursor INTO @containerSqlId, @containerIncId, @storingEventModifiedOn
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @containerPeriodStart = @storingEventModifiedOn
SET @nextStoringEventCursor = CURSOR FOR
SELECT TOP(1) storingEventModifiedOn
FROM StoringsEvents
WHEREstoringEventModifiedOn > @containerPeriodStart
AND itemSqlId = @itemSqlId
AND itemIncId = @itemIncId
AND itemMetaTableSqlId = @itemMetaTableSqlId
AND itemMetaTableIncId = @itemMetaTableIncId
AND isDeleted = 0
ORDER BY storingEventModifiedOn ASC
OPEN @nextStoringEventCursor
FETCH @nextStoringEventCursor INTO @containerPeriodEnd
CLOSE @nextStoringEventCursor
IF(@@FETCH_STATUS <> 0)
SET @containerPeriodEnd = NULL
IF((@periodStart IS NOT NULL) AND (@containerPeriodStart < @periodStart))
SET @containerPeriodStart = @periodStart
IF((@periodEnd IS NOT NULL) AND (@containerPeriodEnd > @periodEnd))
SET @containerPeriodEnd = @periodEnd
EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem @containerPeriodStart, @containerPeriodEnd, @containerSqlId, @containerIncId, 2, 413
SET @parentsCursor = CURSOR FOR
SELECT containerSqlId, containerIncId
FROM [dbo].eufn_e5_eSM_SE_GetParentContainersForItem(@storingEventModifiedOn, @containerSqlId, @containerIncId, 2, 413)
OPEN @parentsCursor
FETCH NEXT FROM @parentsCursor INTO @parentContainerSqlId, @parentContainerIncId
WHILE(@@FETCH_STATUS = 0)
BEGIN
EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem @containerPeriodStart, @containerPeriodEnd, @parentContainerSqlId, @parentContainerIncId, 2, 413
FETCH NEXT FROM @parentsCursor INTO @parentContainerSqlId, @parentContainerIncId
END
CLOSE @parentsCursor
FETCH NEXT FROM @storingEventsCursor INTO @containerSqlId, @containerIncId, @storingEventModifiedOn
END
CLOSE @storingEventsCursor
DROP TABLE #StoringEvents
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetParentContainersForItem]
(
@time DATETIME,
@itemSqlId SMALLINT,
@itemIncId INT,
@itemMetaTableSqlId SMALLINT,
@itemMetaTableIncId INT
)
RETURNS @Parents TABLE
(
containerSqlId SMALLINT,
containerIncId INT,
depth INT
)
AS
BEGIN
-- Declare variables
DECLARE @depth AS INT
DECLARE @containerSqlId SMALLINT
DECLARE @containerIncId INT
SET @containerSqlId = @itemSqlId
SET @containerIncId = @itemIncId
SET @depth = 0
-- Code
WHILE(1=1)
BEGIN
DECLARE @nextContainerSqlId SMALLINT
DECLARE @nextContainerIncId INT
SET @nextContainerSqlId = NULL
SET @nextContainerIncId = NULL
SELECTTOP(1)
@nextContainerSqlId = SE.containerSqlId,
@nextContainerIncId = SE.containerIncId
FROM StoringsEvents SE
WHERESE.itemSqlId = @containerSqlId
AND SE.itemIncId = @containerIncId
AND SE.itemMetaTableSqlId = @itemMetaTableSqlId
AND SE.itemMetaTableIncId = @itemMetaTableIncId
AND SE.storingEventModifiedOn <= @time
AND SE.isDeleted = 0
ORDER BY SE.storingEventModifiedOn DESC
IF(@nextContainerSqlId IS NULL) OR
(@nextContainerIncId IS NULL)
BEGIN
RETURN
END
SET @containerSqlId = @nextContainerSqlId;
SET @containerIncId = @nextContainerIncId;
IF(@containerSqlId = @itemSqlId) AND
(@containerIncId = @itemIncId)
BEGIN
INSERT INTO @Parents
SELECT @containerSqlId, @containerIncId, -1
RETURN -- Cycle detected
END
IF(EXISTS(SELECT *
FROM @Parents P
WHERE(P.containerSqlId = @containerSqlId) AND
(P.containerIncId = @containerIncId)))
BEGIN
INSERT INTO @Parents
SELECT @containerSqlId, @containerIncId, -1
RETURN -- Cycle detected
END
INSERT INTO @Parents
SELECT @containerSqlId, @containerIncId, @depth
SET @itemMetaTableSqlId = 2 -- Containers
SET @itemMetaTableIncId = 413
SET @depth = @depth + 1
END
RETURN
END
March 10, 2016 at 3:47 am
I have debugged and found out that it is failing and throwing the error after this statement in usp_StatusHistory
FETCH @samplePartnerKeyCursor INTO @samplePartnerCode, @clientSampleCode, @internalSampleCode, @samplePartnerSqlId, @samplePartnerIncId
and hence the
CLOSE @samplePartnerKeyCursor is not executing.
Please suggest a way to fix this error.
March 10, 2016 at 3:47 am
Junglee_George (3/10/2016)
I am posting the required code.
ALTER FUNCTION [dbo].[eufn_e5_eLIMS_SE_Parse_Delimited_List](
@listString nvarchar(4000),
@delimiter nvarchar(1)
)
RETURNS @listTable Table( listItem nvarchar(50) )
AS
BEGIN
DECLARE @pos int
DECLARE @itemString nvarchar(50)
SET @listString = LTRIM(RTRIM(@listString))+ @delimiter-- add trailing comma
SET @Pos = CHARINDEX(@delimiter, @listString, 1)-- first delimiter
IF REPLACE(@listString, @delimiter, '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @itemString = LTRIM(RTRIM(LEFT(@listString, @Pos - 1)))
IF @itemString <> ''
BEGIN
INSERT INTO @listTable (listItem) VALUES (@itemString)
END
SET @listString = RIGHT( @listString, LEN(@listString) - @Pos)
SET @Pos = CHARINDEX(@delimiter, @listString, 1)
END
END
RETURN
END
ALTER PROCEDURE [dbo].[eusp_e5_eSM_SE_GetHistoryForItem]
@periodStart DATETIME,
@periodEnd DATETIME,
@itemSqlId SMALLINT,
@itemIncId INT,
@itemMetaTableSqlId SMALLINT,
@itemMetaTableIncId INT
AS
-- Declare temp tables
CREATE TABLE #StoringEvents
(
storingEventSqlId SMALLINT,
storingEventIncId INT,
containerSqlId SMALLINT,
containerIncId INT,
storingEventModifiedOn DATETIME,
storingEventModifiedBySqlId SMALLINT,
storingEventModifiedByIncId SMALLINT,
)
-- Declare variables
DECLARE @storingEventsCursor AS CURSOR
DECLARE @parentsCursor AS CURSOR
DECLARE @containerSqlId AS SMALLINT
DECLARE @containerIncId AS INT
DECLARE @storingEventModifiedOn AS DATETIME
DECLARE @containerPeriodStart DATETIME
DECLARE @containerPeriodEnd DATETIME
DECLARE @parentContainerSqlId SMALLINT
DECLARE @parentContainerIncId INT
DECLARE @debug INT
DECLARE @nextStoringEventCursor CURSOR
SET @debug = 0
SET @storingEventsCursor = CURSOR FOR
SELECTcontainerSqlId,
containerIncId,
storingEventModifiedOn
FROM #StoringEvents
-- Code
IF @debug = 1
SELECT 'START eusp_e5_eSM_SE_GetHistoryForItem', @periodStart AS periodStart, @periodEnd AS periodEnd, @itemSqlId AS itemSqlId, @itemIncId AS itemIncId, @itemMetaTableSqlId AS itemMetaTableSqlId, @itemMetaTableIncId AS itemMetaTableIncId
INSERT INTO #StoringEvents
SELECTstoringEventSqlId,
storingEventIncId,
containerSqlId,
containerIncId,
storingEventModifiedOn,
storingEventModifiedBySqlId,
storingEventModifiedByIncId
FROM StoringsEvents
WHEREitemSqlId = @itemSqlId
AND itemIncId = @itemIncId
AND itemMetaTableSqlId = @itemMetaTableSqlId
AND itemMetaTableIncId = @itemMetaTableIncId
AND typeOfEventSqlId = 117
ANDtypeOfEventIncId = 1
AND (@periodStart IS NULL
OR(storingEventModifiedOn >= @periodStart) )
AND (@periodEnd IS NULL
OR(storingEventModifiedOn <= @periodEnd) )
AND isDeleted = 0
IF @debug = 1
SELECT '#StoringEvents', * FROM #StoringEvents
INSERT INTO #ItemHistory
SELECT * FROM #StoringEvents SE
WHERE (SELECT COUNT(*)
FROM #ItemHistory IH
WHERESE.storingEventSqlId = IH.storingEventSqlId
AND SE.storingEventIncId = IH.storingEventIncId) = 0 -- Insert only once
OPEN @storingEventsCursor
FETCH NEXT FROM @storingEventsCursor INTO @containerSqlId, @containerIncId, @storingEventModifiedOn
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @containerPeriodStart = @storingEventModifiedOn
SET @nextStoringEventCursor = CURSOR FOR
SELECT TOP(1) storingEventModifiedOn
FROM StoringsEvents
WHEREstoringEventModifiedOn > @containerPeriodStart
AND itemSqlId = @itemSqlId
AND itemIncId = @itemIncId
AND itemMetaTableSqlId = @itemMetaTableSqlId
AND itemMetaTableIncId = @itemMetaTableIncId
AND isDeleted = 0
ORDER BY storingEventModifiedOn ASC
OPEN @nextStoringEventCursor
FETCH @nextStoringEventCursor INTO @containerPeriodEnd
CLOSE @nextStoringEventCursor
IF(@@FETCH_STATUS <> 0)
SET @containerPeriodEnd = NULL
IF((@periodStart IS NOT NULL) AND (@containerPeriodStart < @periodStart))
SET @containerPeriodStart = @periodStart
IF((@periodEnd IS NOT NULL) AND (@containerPeriodEnd > @periodEnd))
SET @containerPeriodEnd = @periodEnd
EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem @containerPeriodStart, @containerPeriodEnd, @containerSqlId, @containerIncId, 2, 413
SET @parentsCursor = CURSOR FOR
SELECT containerSqlId, containerIncId
FROM [dbo].eufn_e5_eSM_SE_GetParentContainersForItem(@storingEventModifiedOn, @containerSqlId, @containerIncId, 2, 413)
OPEN @parentsCursor
FETCH NEXT FROM @parentsCursor INTO @parentContainerSqlId, @parentContainerIncId
WHILE(@@FETCH_STATUS = 0)
BEGIN
EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem @containerPeriodStart, @containerPeriodEnd, @parentContainerSqlId, @parentContainerIncId, 2, 413
FETCH NEXT FROM @parentsCursor INTO @parentContainerSqlId, @parentContainerIncId
END
CLOSE @parentsCursor
FETCH NEXT FROM @storingEventsCursor INTO @containerSqlId, @containerIncId, @storingEventModifiedOn
END
CLOSE @storingEventsCursor
DROP TABLE #StoringEvents
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetParentContainersForItem]
(
@time DATETIME,
@itemSqlId SMALLINT,
@itemIncId INT,
@itemMetaTableSqlId SMALLINT,
@itemMetaTableIncId INT
)
RETURNS @Parents TABLE
(
containerSqlId SMALLINT,
containerIncId INT,
depth INT
)
AS
BEGIN
-- Declare variables
DECLARE @depth AS INT
DECLARE @containerSqlId SMALLINT
DECLARE @containerIncId INT
SET @containerSqlId = @itemSqlId
SET @containerIncId = @itemIncId
SET @depth = 0
-- Code
WHILE(1=1)
BEGIN
DECLARE @nextContainerSqlId SMALLINT
DECLARE @nextContainerIncId INT
SET @nextContainerSqlId = NULL
SET @nextContainerIncId = NULL
SELECTTOP(1)
@nextContainerSqlId = SE.containerSqlId,
@nextContainerIncId = SE.containerIncId
FROM StoringsEvents SE
WHERESE.itemSqlId = @containerSqlId
AND SE.itemIncId = @containerIncId
AND SE.itemMetaTableSqlId = @itemMetaTableSqlId
AND SE.itemMetaTableIncId = @itemMetaTableIncId
AND SE.storingEventModifiedOn <= @time
AND SE.isDeleted = 0
ORDER BY SE.storingEventModifiedOn DESC
IF(@nextContainerSqlId IS NULL) OR
(@nextContainerIncId IS NULL)
BEGIN
RETURN
END
SET @containerSqlId = @nextContainerSqlId;
SET @containerIncId = @nextContainerIncId;
IF(@containerSqlId = @itemSqlId) AND
(@containerIncId = @itemIncId)
BEGIN
INSERT INTO @Parents
SELECT @containerSqlId, @containerIncId, -1
RETURN -- Cycle detected
END
IF(EXISTS(SELECT *
FROM @Parents P
WHERE(P.containerSqlId = @containerSqlId) AND
(P.containerIncId = @containerIncId)))
BEGIN
INSERT INTO @Parents
SELECT @containerSqlId, @containerIncId, -1
RETURN -- Cycle detected
END
INSERT INTO @Parents
SELECT @containerSqlId, @containerIncId, @depth
SET @itemMetaTableSqlId = 2 -- Containers
SET @itemMetaTableIncId = 413
SET @depth = @depth + 1
END
RETURN
END
Thanks.
The stored procedure [dbo].[eusp_e5_eSM_SE_GetHistoryForItem] calls itself.
In your testing environment, those nested calls are within bounds, determined by the data you have. In your production environment, the nesting required to resolve item history exceeds SQL Server bounds.
All of the code you've posted on this thread is horrible and exposes a staggering lack of understanding of how SQL Server and TSQL work. Rather than attempting to fix the immediate issue (excessive nesting levels) with this inherited code, do your boss proud, show them what you can do and rewrite the whole lot set-based. Much less code, simpler, and orders of magnitude faster.
You have a head start with the string splitter - replace it with the ssc version written by Jeff Moden et al. I don't think it would take much longer to convert this pile into proper TSQL than it would take just to fix the nesting issue.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 10, 2016 at 4:54 am
I wiil use the NVARCHAR 4K version of [DelimitedSplitN4K] for splitting the input parameter string.
If you can help me in rewriting the stoed proc usp_StatusHistory , it will be helpful.
March 10, 2016 at 6:08 am
I think it's a going to take a good rewrite and step back from trying to modify the existing, and just join these three tables together, and trim off any columns you don't need.
after looking in all the code you posted, i only see three tables actually being used
StoringsEvents (seems to have the dates to match the parameters passed)
SamplesPartners (joined against the parameter to split list that gets passed in)
Operators
you know the tables better than us,
what columns join those three tables together is the direct fix to this. building that basic query outside of looking at the existing procedures is the first thing i would look for.
i cannot tell for sure if it's just an extra cursor, or if it looks like the cursor is joining StoringEvents against itself to supports some sort of hierarchy; i think it's just a design issue.
I would start with a basic query, ge tthe proof of concept going.
SELECT TOP 1 *
FROM StoringsEvents se
INNER JOIN SamplesPartners sp ON se.? = sp.? AND se.?2 = sp.?2
INNER JOIN Operators 0ON sp .? = 0.? AND sp .?2 = 0.?2
--in instead of like? does it need to be loosy joins like that?
WHERE sp.samplePartnerCode IN (SELECT Item FROM DelimitedSplit4K(@specimensCodes,',')
OR sp.clientSampleCode IN (SELECT Item FROM DelimitedSplit4K(@specimensCodes,',')
OR sp.internalSampleCode IN (SELECT Item FROM DelimitedSplit4K(@specimensCodes,',')
Lowell
March 10, 2016 at 6:23 am
Junglee_George (3/10/2016)
I wiil use the NVARCHAR 4K version of [DelimitedSplitN4K] for splitting the input parameter string.If you can help me in rewriting the stoed proc usp_StatusHistory , it will be helpful.
Are you asking me to do your work for you?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 10, 2016 at 8:04 am
ChrisM@Work (3/10/2016)
Junglee_George (3/10/2016)
I wiil use the NVARCHAR 4K version of [DelimitedSplitN4K] for splitting the input parameter string.If you can help me in rewriting the stoed proc usp_StatusHistory , it will be helpful.
Are you asking me to do your work for you?
OP: What you have is VERY complex and a LOT of code, structures, etc. It goes WAY beyond free help on a forum. Chris is right - this is not a small amount of WORK, and for that if you want it done by others you should hire a consultant to do it for you (and hopefully teach you how they did it).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 10, 2016 at 8:16 am
I am encountering an error below which is because of the nested operations in my code
Server: Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view
nesting level exceeded (limit 32).
In the Stored Proc, usp_StatusHistory, there are three cursors used.
I need some guidance to convert them into CTE or other way so that nested operations can be minimized.
Can anyone help me how to do this?
Thanks in advance
March 15, 2016 at 1:53 am
I have added the statement SELECT @@NESTLEVEL in my query at different points in the stored proc eusp_e5_eSM_SE_GetHistoryForItem.
But everywhere it is returning a value 0, even at the previous line just before where it is breaking with error
Why is it so? What else can I do?
March 15, 2016 at 2:45 am
Junglee_George (3/15/2016)
I have added the statement SELECT @@NESTLEVEL in my query at different points in the stored proc eusp_e5_eSM_SE_GetHistoryForItem.But everywhere it is returning a value 0, even at the previous line just before where it is breaking with error
Why is it so? What else can I do?
The code employs nested functions and nested cursors, making it ridiculously complex, awful for performance, and prone to the error you've observed. The only way to deal with this is to completely rewrite it, this time properly, in a set-based fashion. Make a list of the different functional elements required and tackle them separately.
You have a custom string-splitter; replace it with the ssc splitter (get it here[/url], be sure to read and understand the article).
You have some kind of hierarchical data which you're attempting to resolve out using a nested function or whatever with nested cursors - which is completely bonkers. Why? Use a recursive CTE, they're simple to write and work well. I recommend you begin with this part.
If you need more help - and I'm guessing you will from the current state of your code - then post up enough information for folks to give you that help without having to ask you tons of questions to fill in detail gaps.
Here's an opportunity for you to escape from the "TSQL absolute beginner" prison camp. Take it, because what you're trying to do is beyond beginner level.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply