MSSQL: How to use Transaction

Put the following code inside a Store Procedure or inside a function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

BEGIN TRY BEGIN TRAN --Your sql stuff here COMMIT TRAN SELECT CONVERT(BIT,1) AS Success, CONVERT(VARCHAR,NULL) AS Error END TRY BEGIN CATCH ROLLBACK TRAN SELECT CONVERT(BIT,0) AS Success, dbo.fx_get_error() AS Error END CATCH

The following code is just a scalar function to get the error that causes the transaction to roll back.

1 2 3 4 5 6 7 8 9 10 11 12

CREATE FUNCTION [dbo].[fx_get_error]() RETURNS VARCHAR(8000) AS BEGIN RETURN ('Error#: ' + CONVERT(VARCHAR(100),ERROR_NUMBER()) + ', ' + 'Severity: ' + CONVERT(VARCHAR(100),ERROR_SEVERITY()) + ', ' + 'State: ' + CONVERT(VARCHAR(100),ERROR_STATE()) + ', ' + 'Procedure: ' + ERROR_PROCEDURE() + ', ' + 'Line#: ' + CONVERT(VARCHAR(100),ERROR_LINE()) + ', ' + 'Message: ' + ERROR_MESSAGE()) END