Do you write stored procedures that might be used by others?  And those others may or may not have already started a transaction?  And your SP does several things, but if any of them fail, you have to undo them all and return with a code indicating it failed?

Well, I have written such code, and it wasn’t working right until I finally figured out how to handle the case when we are already in a transaction, as well as the case where the caller did not start a transaction.  When a problem occurred, my “ROLLBACK TRANSACTION” would roll back not just my nested transaction, but the caller’s transaction as well.  So when I tested the procedure stand-alone, it seemed to work fine, but when others used it, it would cause a problem if it had to rollback.  When something went wrong in my procedure, their entire transaction was rolled back.  This was not appreciated.

Now, I knew one could "nest" transactions, but the technical documentation was very confusing.  And I still have not found the approach below documented anywhere.  So here is a very brief description of how I got it to work, I hope you find this helpful.

My example is a stored procedure that must figure out on its own if the caller has started a transaction or not.  This can be done in SQL Server by checking the @@TRANCOUNT value.  If no BEGIN TRANSACTION has occurred yet, this will have a value of 0.  Any number greater than zero means that a transaction is in progress.  If there is no current transaction, my SP begins a transaction. But if a transaction is already in progress, my SP uses SAVE TRANSACTION and gives it a name.  SAVE TRANSACTION creates a “save point”.  Note that creating a save point has no effect on @@TRANCOUNT. 

So my SP starts with something like this:

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT

IF @startingTranCount > 0
    SAVE TRANSACTION mySavePointName
ELSE
    BEGIN TRANSACTION
-- …

Then, when ready to commit the changes, you only need to commit if we started the transaction ourselves:

IF @startingTranCount = 0
    COMMIT TRANSACTION

And finally, to roll back just your changes so far:

-- Roll back changes...
IF @startingTranCount > 0
    ROLLBACK TRANSACTION MySavePointName
ELSE
    ROLLBACK TRANSACTION

Here is some code that you can try that will demonstrate how the save points work inside a transaction.

This sample code creates a temporary table, then executes selects and updates, documenting what is going on, then deletes the temporary table.

if running in SQL Management Studio, set Query Results to: Text for best readability of the results.

-- Create a temporary table to test with, we'll drop it at the end.
CREATE TABLE #ATable(
    [Column_A] [varchar](5) NULL
) ON [PRIMARY]

GO
SET NOCOUNT ON
-- Ensure just one row - delete all rows, add one
DELETE #ATable
-- Insert just one row
INSERT INTO #ATable VALUES('000')

SELECT 'Before TRANSACTION starts, value in table is: ' AS Note, * FROM #ATable

SELECT @@trancount AS CurrentTrancount
--insert into a values ('abc')
UPDATE #ATable SET Column_A = 'abc'
SELECT 'UPDATED without a TRANSACTION, value in table is: ' AS Note, * FROM #ATable
BEGIN TRANSACTION
SELECT 'BEGIN TRANSACTION, trancount is now ' AS Note, @@TRANCOUNT AS TranCount
UPDATE #ATable SET Column_A = '123'
SELECT 'Row updated inside TRANSACTION, value in table is: ' AS Note, * FROM #ATable
SAVE TRANSACTION MySavepoint
SELECT 'Save point MySavepoint created, transaction count now:' as Note, @@TRANCOUNT AS TranCount
UPDATE #ATable SET Column_A = '456'
SELECT 'Updated after MySavepoint created, value in table is: ' AS Note, * FROM #ATable
SAVE TRANSACTION point2
SELECT 'Save point point2 created, transaction count now:' as Note, @@TRANCOUNT AS TranCount
UPDATE #ATable SET Column_A = '789'
SELECT 'Updated after point2 savepoint created, value in table is: ' AS Note, * FROM #ATable
ROLLBACK TRANSACTION point2
SELECT 'Just rolled back savepoint "point2", value in table is: ' AS Note, * FROM #ATable
ROLLBACK TRANSACTION MySavepoint
SELECT 'Just rolled back savepoint "MySavepoint", value in table is: ' AS Note, * FROM #ATable
SELECT 'Both save points were rolled back, transaction count still:' as Note, @@TRANCOUNT AS TranCount
ROLLBACK TRANSACTION
SELECT 'Just rolled back the entire transaction..., value in table is: ' AS Note, * FROM #ATable

DROP TABLE #ATable

The output should look like this:

Note                                           Column_A
---------------------------------------------- --------
Before TRANSACTION starts, value in table is:  000

CurrentTrancount
----------------
0

Note                                               Column_A
-------------------------------------------------- --------
UPDATED without a TRANSACTION, value in table is:  abc

Note                                 TranCount
------------------------------------ -----------
BEGIN TRANSACTION, trancount is now  1

Note                                                Column_A
--------------------------------------------------- --------
Row updated inside TRANSACTION, value in table is:  123

Note                                                   TranCount
------------------------------------------------------ -----------
Save point MySavepoint created, transaction count now: 1

Note                                                   Column_A
------------------------------------------------------ --------
Updated after MySavepoint created, value in table is:  456

Note                                              TranCount
------------------------------------------------- -----------
Save point point2 created, transaction count now: 1

Note                                                        Column_A
----------------------------------------------------------- --------
Updated after point2 savepoint created, value in table is:  789

Note                                                     Column_A
-------------------------------------------------------- --------
Just rolled back savepoint "point2", value in table is:  456

Note                                                          Column_A
------------------------------------------------------------- --------
Just rolled back savepoint "MySavepoint", value in table is:  123

Note                                                        TranCount
----------------------------------------------------------- -----------
Both save points were rolled back, transaction count still: 1

Note                                                            Column_A
--------------------------------------------------------------- --------
Just rolled back the entire transaction..., value in table is:  abc