Hi,
Here i am using cursor and i have 189561 record .In these record i am performing updation in same table and insertion in different table.It take much time .is there any alternate to avoid the cursor .I am giving my code which i wriiten .Please check and give me alternate solution.
//code start
ALTER procedure [dbo].[updateNapsRecon]
as
DECLARE @irisRet_Ref_No varchar(50),
@glRet_Ref_No varchar(50),
@irisRecon_id varchar(50),
@glRecon_id varchar(50),
@iris_sid varchar(50),
@gl_sid varchar(50),
@iris_amt varchar(50),
@gl_amt varchar(50),
@genrateRecon_id varchar(50),
@genrateSid varchar(50),
@qcbRet_Ref_No varchar(50),
@qcb_sid varchar(50),
@qcb_amt varchar(50),
@qcb_recon_id varchar(50),
@irisversion int,
@glversion int,
@qcbversion int,
@ErrorMessage varchar(2000)
,@ErrorSeverity tinyint
,@ErrorState tinyint,
@count int=1
-- Here i create cursor name is c3 according to require condition
DECLARE c4 CURSOR FORWARD_only FOR SELECT i.SID,i.RETR_REF_NO,i.AMT_TRAN ,i.RECON_ID,g.SID,g.RETR_REF_NO,g.TRA_AMT ,g.RECON_ID,q.SID,q.RETR_REF_NO,q.TRANS_AMOUNT ,q.RECON_ID
, i.version,g.version,q.version FROM
IRIS_STG i inner join GL_1015_STG g
on i.RETR_REF_NO=g.RETR_REF_NO inner join QCB_STG q on
q.RETR_REF_NO=i.RETR_REF_NO where
q.TRANS_AMOUNT=i.AMT_TRAN and i.AMT_TRAN=
g.TRA_AMT and i.PAN=q.CARD_NUMBER and
i.SYS_TRACE_AUDIT_NO=g.stan
and q.BIN IN(434141,484823,428246,472839)
AND q.CHANNEL='ATM' AND q.ISS_BANK_CODE=3
AND q.ACQ_BANK_CODE!=3
AND q.TRAN_TYPE IN(10,98,99)
AND (q.RECON_ID IS NULL OR q.RECON_STATUS='AU')
and g.AUTHORIZING_CHANNEL IN('9999')
AND g.PROC_CODE=01 AND g.ACQUIRING_CHANNEL_ID=28
AND g.TELL_ID=9953 AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')
and
i.AUTHORIZER IN('9999')
AND i.ACQUIRING_CHANNEL_ID=28 AND i.RESP_CODE IN('000','036','037') AND i.PROC_CODE_FIRST_2=01
AND i.BIN IN(434141,484823,428246,472839)
AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')
-- open the cursor
OPEN c4;
-- fetch one row column value and store in to @colName,@colType
FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@iris_amt,@irisRecon_id,@gl_sid,@glRet_Ref_No,@gl_amt,@glRecon_id,
@qcb_sid,@qcbRet_Ref_No,@qcb_amt,@qcb_recon_id,
@irisversion,@glversion,@qcbversion;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @irisRecon_id is null and @glRecon_id is null and @qcb_recon_id is null
begin
begin try
insert into IRIS_AUDIT select * from IRIS_stg where sid=@iris_sid;
insert into GL_1015_AUDIT select * from GL_1015_STG where sid=@gl_sid;
insert into QCB_AUDIT select * from QCB_STG where sid=@qcb_sid;
set @genrateRecon_id=@irisRet_Ref_No+''+@iris_sid;
set @irisversion=@irisversion+1;
set @glversion=@glversion+1;
set @qcbversion=@qcbversion+1;
update IRIS_STG set RECON_ID=cast(@genrateRecon_id as bigint),RECON_STATUS='AM' ,VERSION=@irisversion where sid=@iris_sid and AMT_TRAN=@iris_amt;
update GL_1015_STG set RECON_ID=cast(@genrateRecon_id as bigint),RECON_STATUS='AM' ,VERSION=@glversion where sid=@gl_sid and TRA_AMT=@gl_amt;
update QCB_STG set RECON_ID=cast(@genrateRecon_id as bigint),RECON_STATUS='AM' ,VERSION=@qcbversion where sid=@qcb_sid and TRANS_AMOUNT=@qcb_amt;
print @count
set @count=@count+1
end try
begin catch
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
BREAK
end catch
end
-- fetch one row column value and store in to @colName,@colType inside loop
FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@iris_amt,@irisRecon_id,@gl_sid,@glRet_Ref_No,@gl_amt,@glRecon_id,
@qcb_sid,@qcbRet_Ref_No,@qcb_amt,@qcb_recon_id,@irisversion,@glversion,@qcbversion;
END
--close while loo body
-- close the cursor c3
CLOSE c4;
--DEALLOCATE cursor c3 that mean every execution time it will treate as new one
DEALLOCATE c4;
//code end
thanks...........