August 10, 2009 at 1:25 am
hi all,
i want to remove this cursor how do i do that as this proc is lots of time.
one thing i know as we can do the bulk update select statement can be remove but how to do that i dnt no
much.please help as i want to remove this cursor
please find the attached proc
August 10, 2009 at 1:58 am
Can you explain what it is that the cursor's doing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2009 at 3:48 am
Without test data, and expected results, it is difficult to tell but maybe something like:
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.P_SRE_T_CAL
@CURDATE datetime,
@ERROR varchar(50) OUTPUT,
@TRANIDUP varchar(8000) OUTPUT
AS
SET NOCOUNT ON
SELECT @ERROR = ''
,@TRANIDUP = ''
BEGIN TRY
UPDATE P_T_D
SET P_C_S_TX =
(
SELECT
CASE
WHEN B.ST_FLG = 'Y' AND B.T_L_FLG = 'L' AND B.SOURCE_FLG ='N'
THEN ROUND((P_T_D.P_C_AMT * B.ST_VAL)/100.0, 2)
ELSE 0
END
FROM P_M_S B
WHERE B.SR_ID = P_T_D.SR_ID
)
,P_REMARKS = 'STAXCALC'
,R_MOD_TIME = GETDATE()
,@TRANIDUP = @TRANIDUP + CAST(TR_ID AS varchar(20)) + ','
WHERE R_CRE_TIME >= DATEADD(d ,-1 ,@CURDATE)
AND R_CRE_TIME < @CURDATE
AND RET_TRAN_STATUS IN ('S','T')
AND EXISTS
(
SELECT *
FROM P_M_S B1
WHERE B1.SR_ID = P_T_D.SR_ID
)
SELECT @ERROR= CAST(@@ROWCOUNT AS varchar(50))
,@TRANIDUP = LEFT(@TRANIDUP, LEN(@TRANIDUP) - 1)
END TRY
BEGIN CATCH
SELECT @ERROR = 'ERROR'--ERROR OCCURED DURING UPDATION
,@TRANIDUP = ''
END CATCH
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply