BEGIN TRANSACTION AND COMMIT TRANSACTION

vanikanc

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

Phanindra261

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