Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

Question

Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I have to agree with everything that has been said so far. This is going to need to be rewritten. You need to step back and work it a piece at a time.

One thing to figure out is what are the pieces doing. For instance, the function [dbo].[eufn_e5_eSM_SE_GetParentContainersForItem]. What exactly is it doing. To start, you the need the DDL (CREATE TABLE statement) for the table(s) that this functions references. You also need sample data (INSERT INTO statements) with enough data to understand what it is returning. From there you can start looking at how this function can be rewritten to perform better.

If you would like help with, post what I told you need above. Please note, we aren't going to do the work for you but help you work through the process to do it yourself. Think of it as being taught how to fish instead of being given a fish. This is necessary as you will be the one having to support this code and you need to understand how it works.

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

I am in the process of converting cursors used in the stored procs to temp table code.

I have started like converting the first cursor, @specimensCodesCursor in stored proc usp_StatusHistory

SET @specimensCodesCursor = CURSOR

FOR SELECT *

FROM dbo.eufn_e5_eLIMS_SE_Parse_Delimited_List (@specimensCodes, ',');

CONVERTED TO

CREATE #Temp_SpecimenCodes(ID INT IDENTITY(1,1), specimensCodes NVARCHAR(4000)

INSERT INTO #Temp_SpecimenCodes

SELECT *

FROM dbo.eufn_e5_eLIMS_SE_Parse_Delimited_List (@specimensCodes, ',');

Please comment whether is this is the way to proceed on this. Thanks in advance.

Lowell SSC Guru Points: 323518 More actions · Answer 3

you have to go deeper.

all that does is turn the string into a table. join that to another real table.

replace the function with dbo.DelimitedSplit8k(@Specimen,','); and CROSS APPLY that against the data that was gettign row by row in the cursor.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lowell (3/15/2016)
you have to go deeper.
all that does is turn the string into a table. join that to another real table.
replace the function with dbo.DelimitedSplit8k(@Specimen,','); and CROSS APPLY that against the data that was gettign row by row in the cursor.

Looking at your code I'd use the dbo.DelimitedSplitN4k since you are using nvarchar data types.

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 5

I have been working on the code, to remove cursors.

But one cursor, still hard to remove. Please guide me on this.

If further improvements are possible, please comment on that too.

CREATE PROCEDURE [dbo].usp_StatusHistory

@specimensCodes NVARCHAR(4000)

AS

SET NOCOUNT ON

-- 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 INT

DECLARE @specimenCode NVARCHAR(25)

DECLARE @samplePartnerCode NVARCHAR(25)

DECLARE @internalSampleCode NVARCHAR(50)

DECLARE @clientSampleCode NVARCHAR(50)

DECLARE @specimensCodesCursor CURSOR

DECLARE @samplePartnerKeyCursor CURSOR

DECLARE @samplePartnerSqlId SMALLINT

DECLARE @samplePartnerIncId INT

DECLARE @itemHistoryCursor CURSOR

DECLARE @modifiedOn DATETIME

DECLARE @typeOfEventName NVARCHAR(50)

DECLARE @parentContainerName NVARCHAR(50)

DECLARE @operatorNameNVARCHAR(120)

SET @debug = 0

CREATE TABLE #Temp_SpecimenCodes(ID INT IDENTITY(1,1), specimensCodes NVARCHAR(4000))

INSERT INTO #Temp_SpecimenCodes

SELECT *

FROM dbo.DelimitedSplitN4K (@specimensCodes, ',')

CREATE TABLE #SamplePartnerKeys (ID INT IDENTITY(1,1),

samplePartnerCode NVARCHAR(50) NOT NULL,

clientSampleCode NVARCHAR(100),

internalSampleCode NVARCHAR(100),

samplePartnerSqlId INT,

samplePartnerIncId INT)

SET @itemHistoryCursor = CURSOR FOR

SELECT IH.storingEventModifiedOn, O.OperatorName

FROM #ItemHistory IH

INNER JOIN Operators O

ONO.OperatorSqlId = IH.storingEventModifiedBySqlId

ANDO.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

-- Get the number of rows in the looping table

DECLARE @RowCount INT

SET @RowCount = (SELECT COUNT(ID) FROM #Temp_SpecimenCodes)

-- Declare an iterator

DECLARE @I INT

DECLARE @j-2 INT

-- Initialize the iterator

SET @I = 1

SET @j-2 = 1

-- Loop through the rows of a table

WHILE (@I <= @RowCount)

BEGIN

INSERT INTO #SamplePartnerKeys

SELECT samplePartnerCode, clientSampleCode, internalSampleCode, samplePartnerSqlId, samplePartnerIncId

FROM SamplesPartners sp

WHERE sp.samplePartnerCode IN (SELECT Item FROM DelimitedSplitN4K(@specimensCodes,','))

OR sp.clientSampleCode IN (SELECT Item FROM DelimitedSplitN4K(@specimensCodes,','))

OR sp.internalSampleCode IN (SELECT Item FROM DelimitedSplitN4K(@specimensCodes,','))

WHILE (@J <= (SELECT COUNT(*) FROM #SamplePartnerKeys))

BEGIN

IF @debug = 1

SELECT @samplePartnerSqlId = SPK.samplePartnerSqlId,

@samplePartnerIncId = SPK.samplePartnerIncId FROM #SamplePartnerKeys SPK

EXEC [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 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 SPK.samplePartnerCode , SPK.clientSampleCode , SPK.internalSampleCode, @modifiedOn, @operatorName, @location

FROM #SamplePartnerKeys SPK

FETCH NEXT FROM @itemHistoryCursor INTO @modifiedOn, @operatorName

END

CLOSE @itemHistoryCursor

DELETE FROM #ItemHistory

SET @j-2 = @j-2 + 1

END

SET @I = @I + 1

END

DROP TABLE #ItemHistory

SELECT *

FROM #Result

ORDER BY internalSampleCode ASC, clientSampleCode ASC, dateIn ASC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

I'd strongly urge you to spec out the functionality of

EXEC [dbo].eusp_e5_eSM_SE_GetHistoryForItem

and focus on this. It's the meat on the bones of this report.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

VSSGeorge (3/16/2016)
I have been working on the code, to remove cursors.
But one cursor, still hard to remove. Please guide me on this.
If further improvements are possible, please comment on that too.
CREATE PROCEDURE [dbo].usp_StatusHistory
@specimensCodes NVARCHAR(4000)
AS
SET NOCOUNT ON
-- 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 INT
DECLARE @specimenCode NVARCHAR(25)
DECLARE @samplePartnerCode NVARCHAR(25)
DECLARE @internalSampleCode NVARCHAR(50)
DECLARE @clientSampleCode NVARCHAR(50)
DECLARE @specimensCodesCursor CURSOR
DECLARE @samplePartnerKeyCursor CURSOR
DECLARE @samplePartnerSqlId SMALLINT
DECLARE @samplePartnerIncId INT
DECLARE @itemHistoryCursor CURSOR
DECLARE @modifiedOn DATETIME
DECLARE @typeOfEventName NVARCHAR(50)
DECLARE @parentContainerName NVARCHAR(50)
DECLARE @operatorNameNVARCHAR(120)
SET @debug = 0
CREATE TABLE #Temp_SpecimenCodes(ID INT IDENTITY(1,1), specimensCodes NVARCHAR(4000))
INSERT INTO #Temp_SpecimenCodes
SELECT *
FROM dbo.DelimitedSplitN4K (@specimensCodes, ',')
CREATE TABLE #SamplePartnerKeys (ID INT IDENTITY(1,1),
samplePartnerCode NVARCHAR(50) NOT NULL,
clientSampleCode NVARCHAR(100),
internalSampleCode NVARCHAR(100),
samplePartnerSqlId INT,
samplePartnerIncId INT)
SET @itemHistoryCursor = CURSOR FOR
SELECT IH.storingEventModifiedOn, O.OperatorName
FROM #ItemHistory IH
INNER JOIN Operators O
ONO.OperatorSqlId = IH.storingEventModifiedBySqlId
ANDO.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
-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ID) FROM #Temp_SpecimenCodes)
-- Declare an iterator
DECLARE @I INT
DECLARE @j-2 INT
-- Initialize the iterator
SET @I = 1
SET @j-2 = 1
-- Loop through the rows of a table
WHILE (@I <= @RowCount)
BEGIN
INSERT INTO #SamplePartnerKeys
SELECT samplePartnerCode, clientSampleCode, internalSampleCode, samplePartnerSqlId, samplePartnerIncId
FROM SamplesPartners sp
WHERE sp.samplePartnerCode IN (SELECT Item FROM DelimitedSplitN4K(@specimensCodes,','))
OR sp.clientSampleCode IN (SELECT Item FROM DelimitedSplitN4K(@specimensCodes,','))
OR sp.internalSampleCode IN (SELECT Item FROM DelimitedSplitN4K(@specimensCodes,','))
WHILE (@J <= (SELECT COUNT(*) FROM #SamplePartnerKeys))
BEGIN
IF @debug = 1
SELECT @samplePartnerSqlId = SPK.samplePartnerSqlId,
@samplePartnerIncId = SPK.samplePartnerIncId FROM #SamplePartnerKeys SPK
EXEC [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 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 SPK.samplePartnerCode , SPK.clientSampleCode , SPK.internalSampleCode, @modifiedOn, @operatorName, @location
FROM #SamplePartnerKeys SPK
FETCH NEXT FROM @itemHistoryCursor INTO @modifiedOn, @operatorName
END
CLOSE @itemHistoryCursor
DELETE FROM #ItemHistory
SET @j-2 = @j-2 + 1
END
SET @I = @I + 1
END
DROP TABLE #ItemHistory
SELECT *
FROM #Result
ORDER BY internalSampleCode ASC, clientSampleCode ASC, dateIn ASC

Note, replacing a cursor with a WHILE loop is not replacing a cursor.

Please look back at my previous post. If you want help learning what needs to be done you need to provide us with DDL for the tables, sample data to be put in the tables, and expected results for the results of each piece. Then we can begin to help you work through th rewrite process.