SQLTeam.com | Weblogs | Forums

Need help to convert a cursor to a temp table code


#1

I am working this stored proc which was there in my DB , part of early development.
I am in the process. of performance tuning by removing the cursors used.
Please guide me in this process.

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;

I have started like converting the first cursor, @specimensCodesCursor

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.