How can i convert While loop by using CTE.

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources