|
Hello All,
If I use a try-catch block in my stored proc, and in the catch block write error to table, do I need to use BEGIN TRANSACTION - COMMIT TRANSACTION to get the try-catch block to work?
I am using -
BEGIN TRY
write my code here
END TRY
BEGIN CATCH
write my code here
END CATCH
But should be - to write errors to a log table.
BEGIN TRY
BEGIN TRANSACTION
write code here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
write code here
END CATCH
|
|
|
|
|
no you do not have to(but it is a safer option)..Have a look at the following snippet..-
USE AdventureWorks2008R2;
GO
-- Variable to store ErrorLogID value of the row
-- inserted in the ErrorLog table by uspLogError
DECLARE @ErrorLogID INT;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Call procedure to print error information.
EXECUTE dbo.uspPrintError;
-- Roll back any active or uncommittable transactions before
-- inserting information in the ErrorLog.
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
-- Retrieve logged error information.
SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;
GO
|
|
|
|