BEGIN TRANSACTION AND COMMIT TRANSACTION
-
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
-
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