Template of a Stored Procedure with Savepoint

Savepoint is used for selective roll back. Using savepoint, a transaction can roll back to a selected location that has been marked. When it is rolled back, in the end the transaction must be completed by using 'commit transaction' or rolled back altogether.

Savepoint name should be unique even though duplicate is allowed. If a roll back is occurred where there is a duplicate, the transaction will be rolled back to the latest savepoint.

CREATE PROCEDURE [Procedure_Name]
AS
BEGIN

-- generate a unique savepoint name by appending procedure name (OBJECT_NAME(@@procid)) and nested level (@@nestlevel)
-- we could also use only @@nestlevel as it will always be unique in an active connection
-- savepoint name's maximum length is limited to 32 characters only
DECLARE @savepoint NVARCHAR(32) = CAST (OBJECT_NAME(@@procid) AS NVARCHAR(29)) +
           CAST (@@nestlevel AS NVARCHAR(3))

-- this is to check whether nested transactions exist when entering this procedure,
--  the value will be used later for checking condition
DECLARE @entryTrancount INT = @@trancount

BEGIN TRY
 BEGIN TRANSACTION
 SAVE TRANSACTION @savepoint
 
 --do something here
 
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 -- transaction is uncommittable (XACT_STATE() = -1) and no nested transactions exist (@entryTrancount = 0)
 IF XACT_STATE() = -1 AND @entryTrancount = 0
  ROLLBACK TRANSACTION
 -- otherwise if transaction is committable
 ELSE IF XACT_STATE() = 1    
  BEGIN
   ROLLBACK TRANSACTION @savepoint
   COMMIT TRANSACTION
  END
   
 DECLARE @ERROR_MESSAGE NVARCHAR(4000)
 SET @ERROR_MESSAGE = 'Error occured in procedure ''' + OBJECT_NAME(@@procid)
       + ''', Original Message: ''' + ERROR_MESSAGE() + ''''
 RAISERROR (@ERROR_MESSAGE, 16, 1)
 RETURN -100
END CATCH
END

According to MSDN, XACT_STATE function returns three values:
1 - The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
0 - There is no active user transaction for the current request.
-1 - The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.


References and further reading:
http://msdn.microsoft.com/en-us/library/ms188378%28v=SQL.105%29.aspx
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson
http://msdn.microsoft.com/en-us/library/ms189797.aspx
http://dosql.com/cms/index.php?option=com_content&view=article&id=101:trancount-and-xactstate&catid=40:microsoft-sql-server&Itemid=41