This page has 0 threads | Add post

Transaction Savepoints

Written by Andrew Gould

In this tutorial

Creating and Rolling Back to a Savepoint
Using Savepoints in Stored Procedures

Normally, when you rollback a transaction SQL Server will undo everything that has happened since the transaction began. You can, however, provide yourself with a way to rollback only part of a transaction using something called a savepoint.

Creating and Rolling Back to a Savepoint

You can create a savepoint at any stage after beginning a transaction, as shown in the code below:

BEGIN TRAN Tran1

INSERT INTO tblTest(TestName)

VALUES ('A')

SAVE TRAN Savepoint

INSERT INTO tblTest(TestName)

VALUES ('B')

ROLLBACK TRAN Savepoint

INSERT INTO tblTest(TestName)

VALUES ('C')

COMMIT TRAN Tran1

Once a savepoint has been created you can choose to rollback to that point at any stage up until you commit or rollback the entire transaction. In the code above, anything that is done between SAVE TRAN and ROLLBACK TRAN will be rolled back, while anything that occurs before or after these lines will be committed.

If we examine the contents of tblTest after executing the code we would see the following values:

The second value that was added to the table doesn't appear because we rolled back this section of the transaction.

Using Savepoints in Stored Procedures

If you begin a transaction in one procedure and then call another procedure the transaction remains open. You can create a savepoint within the called procedure to allow you to rollback any actions that are performed within it.

The code below shows a simple procedure to add a new film to our database. Within this procedure we call another one to assign the correct director to the film.

CREATE PROC spAddFilm(

@Title VARCHAR(MAX)

,@ReleaseDate DATETIME

,@Director VARCHAR(MAX))

BEGIN

DECLARE @FilmID INT

DECLARE @DirectorID INT

BEGIN TRAN AddFilm

--Add a new record to the Film table

INSERT INTO tblFilm(FilmName, FilmReleaseDate)

VALUES (@Title, @ReleaseDate)

--Store the Id of the film just added

SET @FilmID = @@IDENTITY

--Call stored procedure to get the DirectorID

EXEC @DirectorID = spAssignDirector @Director

--Assign the DirectorID to the film added earlier

UPDATE tblFilm

SET FilmDirectorID = @DirectorID

WHERE FilmID = @FilmID

COMMIT TRAN AddFilm

END

The procedure that is called in the middle of this one is used to retrieve the Id number of the film's director. The code for this is shown below:

CREATE PROC spAssignDirector(

@Director VARCHAR(MAX))

BEGIN

DECLARE @ID INT

--Create a savepoint

SAVE TRAN AddDirector

--Insert the new director name into the table

INSERT INTO tblDirector(DirectorName)

VALUES (@DirectorName)

--If there is more than one director with the same name,

--Rollback to the savepoint

IF (SELECT COUNT(*) FROM tblDirector WHERE DirectorName=@Director) > 1

ROLLBACK TRAN AddDirector

--Store the ID of the director

SELECT @ID = DirectorID

FROM tblDirector

WHERE DirectorName = @Director

--Return the DirectorID to the calling procedure

RETURN @ID

END

This procedure creates a savepoint before it attempts to modify any data. It then inserts a new record into the director table and checks to see if there is more than one director with the same name. If that's true then the transaction is rolled back to the savepoint created at the start of this procedure, without affecting any actions that have taken place in the calling procedure.

This is a fairly inefficient way to achieve the result we want! We're using this method purely to demonstrate how savepoints work in stored procedures.

Previous
tutorial

Dynamic SQL

Previous
part

Nesting Transactions

Top of page

Next
part

Next
tutorial

Cursors

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Transactions

Transaction Savepoints

Creating and Rolling Back to a Savepoint

Using Savepoints in Stored Procedures

Head office

London

Manchester