Edit

Share via

SAVE TRANSACTION (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Sets a savepoint within a transaction.

Transact-SQL syntax conventions

Syntax

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]

Arguments

savepoint_name

Is the name assigned to the savepoint. Savepoint names must conform to the rules for identifiers, but are limited to 32 characters. savepoint_name is always case sensitive, even when the Database Engine instance isn't case sensitive.

@savepoint_variable

Is the name of a user-defined variable containing a valid savepoint name. The variable must be declared with a char, varchar, nchar, or nvarchar data type. More than 32 characters can be passed to the variable, but only the first 32 characters are used.

Remarks

You can set a savepoint within a transaction. The savepoint defines a state of consistency to which a transaction can return if a part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.

Duplicate savepoint names are allowed in a transaction, but a ROLLBACK TRANSACTION statement that specifies the savepoint name only rolls the transaction back to the most recent SAVE TRANSACTION using that name.

SAVE TRANSACTION isn't supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or promoted from a local transaction.

Note

The Database Engine doesn't support independently manageable nested transactions. A commit of an inner transaction decrements @@TRANCOUNT but has no other effects. A rollback of an inner transaction always rolls back the outer transaction, unless a savepoint exists and is specified in the ROLLBACK statement.

Locking behavior

A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, except for escalated and converted locks. These locks aren't released, and they aren't converted back to their previous lock mode.

Permissions

Requires membership in the public role.

Examples

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

The following example shows how to use a transaction savepoint to roll back only the modifications made by a stored procedure if a transaction is started before the stored procedure is executed.

IF EXISTS (SELECT name FROM sys.objects
           WHERE name = N'SaveTranExample')
    DROP PROCEDURE SaveTranExample;
GO

CREATE PROCEDURE SaveTranExample
    @InputCandidateID INT
AS
-- Detect whether the procedure was called
-- from an active transaction and save
-- that for later use.
-- In the procedure, @TranCounter = 0
-- means there was no active transaction
-- and the procedure started one.
-- @TranCounter > 0 means an active
-- transaction was started before the
-- procedure was called.
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;

IF @TranCounter > 0
    -- Procedure called when there is
    -- an active transaction.
    -- Create a savepoint to be able
    -- to roll back only the work done
    -- in the procedure if there is an
    -- error.
    SAVE TRANSACTION ProcedureSave;
ELSE
    -- Procedure must start its own
    -- transaction.
    BEGIN TRANSACTION;
-- Modify database.
BEGIN TRY
    DELETE HumanResources.JobCandidate
        WHERE JobCandidateID = @InputCandidateID;
    -- Get here if no errors; must commit
    -- any transaction started in the
    -- procedure, but not commit a transaction
    -- started before the transaction was called.
    IF @TranCounter = 0
        -- @TranCounter = 0 means no transaction was
        -- started before the procedure was called.
        -- The procedure must commit the transaction
        -- it started.
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- An error occurred; must determine
    -- which type of rollback will roll
    -- back only the work done in the
    -- procedure.
    IF @TranCounter = 0
        -- Transaction started in procedure.
        -- Roll back complete transaction.
        ROLLBACK TRANSACTION;
    ELSE
        -- Transaction started before procedure
        -- called, do not roll back modifications
        -- made before the procedure was called.
        IF XACT_STATE() <> -1
            -- If the transaction is still valid, just
            -- roll back to the savepoint set at the
            -- start of the stored procedure.
            ROLLBACK TRANSACTION ProcedureSave;
            -- If the transaction is uncommitable, a
            -- rollback to the savepoint is not allowed
            -- because the savepoint rollback writes to
            -- the log. Just return to the caller, which
            -- should roll back the outer transaction.

    -- After the appropriate rollback, return error
    -- information to the caller.
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();

    RAISERROR (
              @ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );
END CATCH
GO

Related content

  • Last updated on