Dear All
I have written a stored procedure where in the try block i am opening a xml document. I am closing the xml document in the try block itself. In the catch i am just rolling back the transaction. Do i have to close the xml document in the catch block too?
here is the code
Code:
IF(@Mode ='INSERT')
BEGIN TRY
BEGIN TRANSACTION-- Start the transaction
-- INSERTING THE RECORD(S)
-- OPENING THE XML DOCUMENT
EXEC SP_XML_PREPAREDOCUMENT @hdoc OUTPUT,@XmlForm
INSERT INTO HR_EMP_RETIRE_DTLS(Empno,Remarks,Active,Created_By,Created_Date)
SELECT EMPNO,REMARKS,'N',@Created_By,GETDATE()
FROM OPENXML(@hdoc,'/NewDataSet/Table1',2)
WITH (EMPNO numeric(6),REMARKS varchar(1000))
COMMIT TRANSACTION-- Committing the transaction
-- CLOSING THE XML DOCUMENT
EXEC SP_XML_REMOVEDOCUMENT @hdoc
END TRY
-- ERROR HANDLING
BEGIN CATCH
IF @@TRANCOUNT> 0
ROLLBACK TRANSACTION-- Rolling back the transaction
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg =ERROR_MESSAGE(),
@ErrSeverity =ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
-- Abhishek
doubt in Catch block
