Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How can i convert While loop by using CTE.

Author  Topic 

parduman9
Starting Member

1 Post

Posted - 2015-04-23 : 00:48:23
How can i convert the while loop by using cte or any other optimized way?

Following is the current code.

ALTER PROCEDURE USPCalculateRuleValidity
@piRequestID VARCHAR (20), @piImplementationState CHAR (2)
AS
SET NOCOUNT ON;


DECLARE @vModuleName AS VARCHAR (100) = 'USPCalculateRuleValidity',
@vDebugFlag AS VARCHAR (3) = 'YES',
@vErrPoint AS VARCHAR (1000),
@vRecordCount AS INT,
@vErrMesg AS VARCHAR (1000),
@vTimePntCnt AS INT,
@vIter AS INT = 1,
@vPrevTP AS DATE,
@vCurrTP AS DATE,
@vNextTP AS DATE,
@vProjectModelCode CHAR(5),
@vProjectModelID INTEGER,
@vI_XREF INTEGER;

DECLARE @RuleImplementationDate AS TABLE (
C_RUL_TYP VARCHAR (128) ,
RuleBody VARCHAR (4000),
ImplementationStartDate DATE ,
ImplementationEndDate DATE );

DECLARE @FinalRule AS TABLE (
C_RUL_TYP VARCHAR (128) ,
RuleBody VARCHAR (4000),
I_TIMING_PNT INT ,
ProjectModelID INT );

BEGIN
BEGIN TRY
SET @vDebugFlag = (SELECT dbo.UspGetDebugFlagFnc(@vModuleName));

SET @vErrPoint = @piRequestID + ': Start of Procedure ***********';

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

SET @vErrPoint = @piRequestID + ':Getting Project Models';

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

SELECT DISTINCT @vProjectModelCode=pm.ProjectModelCode,
@vProjectModelID=pm.ProjectModelID
FROM RuleMaster AS rm, ProjectModel AS pm
WHERE I_REQ = @piRequestID
AND rm.ProjectModelID = pm.ProjectModelID
AND rm.DeleteFlag = 'N'
AND rm.ErrorFlag IS NULL;

SET @vRecordCount = @@ROWCOUNT;

/*IF @vRecordCount = 0
RAISERROR ('ERROR', 16, 1);

*/

SET @vErrPoint = @piRequestID + ':Getting XREF.Record count is ' + CAST (@vRecordCount AS VARCHAR);;

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

SELECT DISTINCT @vI_XREF=I_XREF
FROM WPCSRVC_Primary.dbo.WPC_CODE_XREF
WHERE I_REQ = @piRequestID;

SET @vRecordCount = @@ROWCOUNT;

/*IF @vRecordCount = 0
RAISERROR ('ERROR', 16, 1);

*/

SET @vErrPoint = @piRequestID + ':Calling Procedure USPCalculateRulesPerXREF.Record count is ' + CAST (@vRecordCount AS VARCHAR);;

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

EXEC USPCalculateRulesPerXREF @piRequestID,@vProjectModelID;

SET @vRecordCount = @@ROWCOUNT;

SET @vErrPoint = 'Selecting Time Points.Record count is ' + CAST (@vRecordCount AS VARCHAR);

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

SELECT ROW_NUMBER() OVER ( ORDER BY ImplementationDate) AS RowNum,
ConfigImplementationID,
ProjectModelID,
ModificationCode,
ImplementationDate,
ImplementationState,
I_XREF,
I_TIMING_PNT,
DeleteFlag,
C_APPROVAL_STATE
INTO #ConfigImplementation
FROM ConfigImplementation
WHERE ProjectModelID = @vProjectModelID
AND ImplementationState = @piImplementationState;

SET @vTimePntCnt = @@Rowcount;

WHILE @vIter < @vTimePntCnt
BEGIN
SET @vErrPoint = 'Extracting Previous, Current & Next TPs. @vIter=' + CAST (@vIter AS VARCHAR);

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

SELECT @vPrevTP = ImplementationDate
FROM #ConfigImplementation
WHERE RowNum = @vIter - 1;

SELECT @vCurrTP = ImplementationDate
FROM #ConfigImplementation
WHERE RowNum = @vIter;

SELECT @vNextTP = ImplementationDate
FROM #ConfigImplementation
WHERE RowNum = @vIter + 1;

INSERT INTO @FinalRule
SELECT rt.C_RUL_TYP,rt.RuleBody ,rt.I_TIMING_PNT,rt.ProjectModelID
FROM RuleTPs AS rt, #ConfigImplementation AS ci
WHERE rt.I_TIMING_PNT = ci.I_TIMING_PNT
AND rt.ProjectModelID = ci.ProjectModelID
AND ci.RowNum = @vIter;

SET @vErrPoint = 'Merging Rules. Record count is ' + CAST (@vRecordCount AS VARCHAR);

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

MERGE INTO @RuleImplementationDate
AS rid
USING @FinalRule AS fr ON rid.RuleBody = fr.RuleBody
AND rid.ImplementationEndDate = @vCurrTP
AND rid.C_RUL_TYP=fr.C_RUL_TYP
WHEN MATCHED THEN
UPDATE
SET ImplementationEndDate = @vNextTP

WHEN NOT MATCHED THEN
INSERT (C_RUL_TYP, RuleBody, ImplementationStartDate, ImplementationEndDate)
VALUES (fr.C_RUL_TYP, fr.RuleBody, @vCurrTP, @vNextTP);


DELETE @FinalRule;

SET @vIter = @vIter + 1;

END
SELECT *
FROM @RuleImplementationDate;

SET @vErrPoint = 'End of Procedure ***********';

EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, @vDebugFlag, @vErrPoint OUTPUT;

END TRY
BEGIN CATCH
IF ERROR_MESSAGE() = 'ERROR' -- This is the case when error is raised using RAISERROR procedure
BEGIN
IF @vErrPoint LIKE '%Getting Project Models%'
SET @vErrMesg = 'No Project Model Exists for the Request ID : ' + @piRequestID;

SET @vErrPoint = 'ERROR: ' + @vErrMesg;

END
ELSE
BEGIN
SET @vErrMesg = 'Runtime error occured. Please contact the developer.';

SET @vErrPoint = 'ERROR: Error occured from ' + @vErrPoint + ' and Error message is ' + ISNULL(ERROR_MESSAGE(), 'Others');

PRINT @vErrPoint;

END
EXECUTE dbo.UspLogDebugMessage @vErrPoint, @vModuleName, 'Yes', @vErrPoint OUTPUT;

END CATCH
END

Kristen
Test

22859 Posts

Posted - 2015-04-23 : 03:35:35
Dunno if it will help much but:

SELECT @vPrevTP = ImplementationDate
FROM #ConfigImplementation
WHERE RowNum = @vIter - 1;

SELECT @vCurrTP = ImplementationDate
FROM #ConfigImplementation
WHERE RowNum = @vIter;

SELECT @vNextTP = ImplementationDate
FROM #ConfigImplementation
WHERE RowNum = @vIter + 1;

INSERT INTO @FinalRule
SELECT rt.C_RUL_TYP,rt.RuleBody ,rt.I_TIMING_PNT,rt.ProjectModelID
FROM RuleTPs AS rt,
#ConfigImplementation AS ci
WHERE rt.I_TIMING_PNT = ci.I_TIMING_PNT
AND rt.ProjectModelID = ci.ProjectModelID
AND ci.RowNum = @vIter;

could be coded as this

INSERT INTO @FinalRule
SELECT rt.C_RUL_TYP,rt.RuleBody ,rt.I_TIMING_PNT,rt.ProjectModelID
FROM RuleTPs AS rt,
#ConfigImplementation AS ci -- @vCurrTP = ci.ImplementationDate
LEFT OUTER JOIN #ConfigImplementation AS C0 -- @vPrevTP = C0.ImplementationDate
ON C0.RowNum = @vIter - 1
LEFT OUTER JOIN #ConfigImplementation AS C1 -- @vNextTP = C1.ImplementationDate
ON C1.RowNum = @vIter + 1

WHERE rt.I_TIMING_PNT = ci.I_TIMING_PNT
AND rt.ProjectModelID = ci.ProjectModelID
AND ci.RowNum = @vIter;

(although @vPrevTP is unused in the code, so superfluous??)

So perhaps you could than change the MERGE to be:

MERGE INTO @RuleImplementationDate
AS rid
USING
(
SELECT rt.C_RUL_TYP,rt.RuleBody ,rt.I_TIMING_PNT,rt.ProjectModelID,
ci.ImplementationDate AS [vCurrTP], C1.ImplementationDate AS [vNextTP]

FROM RuleTPs AS rt,
#ConfigImplementation AS ci
/** UNUSED
LEFT OUTER JOIN #ConfigImplementation AS C0
ON C0.RowNum = @vIter - 1
UNUSED **/
LEFT OUTER JOIN #ConfigImplementation AS C1
ON C1.RowNum = @vIter + 1
WHERE rt.I_TIMING_PNT = ci.I_TIMING_PNT
AND rt.ProjectModelID = ci.ProjectModelID
AND ci.RowNum = @vIter;

) AS fr ON rid.RuleBody = fr.RuleBody
AND rid.ImplementationEndDate = vCurrTP
AND rid.C_RUL_TYP=fr.C_RUL_TYP
WHEN MATCHED THEN
UPDATE
SET ImplementationEndDate = @vNextTP

WHEN NOT MATCHED THEN
INSERT (C_RUL_TYP, RuleBody, ImplementationStartDate, ImplementationEndDate)
VALUES (fr.C_RUL_TYP, fr.RuleBody, vCurrTP, vNextTP);

and then avoid the whole loop?
Go to Top of Page
   

- Advertisement -