I have a Cursor to be replaced build based on if exists condition , Cursor needs to be replaced because of performance issues.I am missing logic behind the If exists condition, So if any alternative to this Cursor with a similar Code is appreciated.
--Original Cursor to be replaced
DECLARE @sCaseID as int
DECLARE @sLastUpdate as datetime
DECLARE @sEntryDate as datetime
DECLARE @sUserID as varchar(30)
DECLARE @sSystem as varchar(4)
DECLARE @sPrin as varchar(4)
DECLARE @sAgent as varchar(4)
DECLARE @sAccountNumber as varchar(16)
DECLARE @sExtStatus as datetime
DECLARE @sIntStatus as varchar(1)
DECLARE sample_cursor CURSOR FOR
Select
S.CaseID, S.LastUpdateDate,S.EntryDate,S.UserId,S.System,S.Prin,S.Agent,
S.AccountNumber,S.IntStatus,substring(S.ExtStatus,1,1) as ExtStatus
FROM dbo.uvwView S
LEFT JOIN dbo.tbl1 A
ON S.AccountNumber = A.AccountNumber
LEFT JOIN dbo.tbl2 C
ON S.ECC = C.ECC
LEFT JOIN dbo.tbl3 T
ON S.Col1 = T.Col1 and
S.Col2 = T.Col2 and
S.Col3 = T.Col3
LEFT JOIN dbo.tbl3 D
ON S.Col4 = D.Col1
OPEN sample_cursor
FETCH NEXT FROM sample_cursor INTO
@sCaseID, @sLastUpdate,@sEntryDate,@sUserID,@sSystem,@sPrin,@sAgent,@sAccountNumber,
@sExtStatus,@sIntStatus
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(
SELECT 1
FROM TargetTbl TT
WHERE TT.CaseID = @sCaseID
OR (TT.CaseID IS NULL
AND TT.AccountNumber = @sAccountNumber
AND ISNULL(TT.CaseNumber,@sCaseNumber) = @sCaseNumber))
BEGIN
UPDATE TargetTbl
SET Col1 = '',
Col2 = '',
Col3 = ''
END
ELSE
BEGIN
INSERT INTO TargetTbl
Values('','','')
FETCH NEXT FROM sample_cursor INTO
@sCaseID, @sLastUpdate,@sEntryDate,@sUserID,@sSystem,@sPrin,@sAgent,@sAccountNumber,
@sExtStatus,@sIntStatus
END
CLOSE sample_cursor
DEALLOCATE sample_cursor
Thanks
Srikanth
No comments:
Post a Comment