Hi All
I have written a stored procedure where i am opening an xml and closing the xml after doing the transaction.
Here is the stored procedure
Code:
ALTERPROCEDURE [dbo].[HR_SP_SWIPE_REGULARIZATION_APPROVAL](@SessionId numeric(7),@Mode varchar(50),
@SwipeXml text,@FrwdAuthority numeric(7),
@ActionType varchar(30))
AS
DECLARE @hdoc int
IF(@Mode ='UPDATE')
BEGIN
IF(@ActionType ='APPROVE')
BEGINTRY
BEGINTRANSACTION-- Beginning the transaction
EXECSP_XML_PREPAREDOCUMENT @hdoc OUTPUT,@SwipeXml -- Preparing the XML document
SELECT EMPNO,REFNO,NONSWIP_DATE,REMARKSPM,SLNO
INTO #TEMPSWIPEAPPROVE
FROMOPENXML(@hDoc,'/NewDataSet/APPROVE',2)
WITH(EMPNO NUMERIC(7),REFNO NUMERIC(7),NONSWIP_DATE VARCHAR(10),REMARKSPM VARCHAR(50),SLNO NUMERIC(7))--select * from #TEMPSWIPEAPPROVE
UPDATE ATTENDANCE_DETAILS
SET
STATUS ='AA',
REMARKSPM = A.REMARKSPM,
APPROVED_DATE =GETDATE(),
MODIFIED_DATE =GETDATE(),
TRANS_STAT ='Y',
MODIFIED_BY = @SessionId
FROM(SELECT*FROM #TEMPSWIPEAPPROVE)A
WHERE ATTENDANCE_DETAILS.REFNO = A.REFNO
AND ATTENDANCE_DETAILS.SLNO = A.SLNO
AND ATTENDANCE_DETAILS.EMPNO = A.EMPNO
AND ATTENDANCE_DETAILS.NONSWIP_DATE = A.NONSWIP_DATE
--To update swipe details
UPDATE SWIPE_DETAILS SET LEAVE_STATUS='AA',REMARKS='Regularization Approved',
MODIFIED_DATE =GETDATE(),
MODIFIED_STATUS=CASEWHEN MODIFIED_STATUS='NA'THEN'NR'
WHEN MODIFIED_STATUS='AN'THEN'RN'
WHEN MODIFIED_STATUS='XA'THEN'XR'
WHEN MODIFIED_STATUS='AX'THEN'RX'
ELSE'RR'END
FROM(SELECT*FROM #TEMPSWIPEAPPROVE)A
WHERE SWIPE_DETAILS.CARDID=A.EMPNO AND
SWIPE_DETAILS.ATTNDATE=CAST(A.NONSWIP_DATE ASDATETIME)
AND A.REFNO=REFNO
COMMITTRANSACTION-- Committing the transaction
EXECSP_XML_REMOVEDOCUMENT @hdoc -- Removing the xml document
DROPTABLE #TEMPSWIPEAPPROVE
ENDTRY
-- ERROR HANDLING
BEGINCATCH
--IF @@TRANCOUNT > 0
ROLLBACKTRANSACTION-- Rolling back the transaction
EXECSP_XML_REMOVEDOCUMENT @hdoc -- Removing the XML document
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
SET @ErrMsg = @ErrMsg + @SessionId
RAISERROR(@ErrMsg, @ErrSeverity, 1)
IFOBJECT_ID('#TEMPSWIPEAPPROVE')ISNOTNULL
DROPTABLE #TEMPSWIPEAPPROVE
ENDCATCH
Doubts:
1) Here if the error occurs in try block, then after entering into catch block, will it remove the xml document or not? or xml document automatically gets removed when error occurs in try block.
2) If any table gets locked in try block, supposingly here in this case swipe details table then how to handle this dead lock condition so that sql server time out error doesn't happen.(Please suppose that this table swipe_details is getting a record no. of hits.)
-- Please throw some light.
-- Abhishek
doubt in try catch functionality
