In SQL Server, transactions are used to execute a group of SQL statements. With transactions, either all the statements in a group execute or none of them execute. However, there may be situations where you need to manually rollback a transaction based on certain conditions. This is where the rollback SQL statement comes into play.

Let’s start by creating a dummy dataset for practice. We will create a dummy database named BookStore with a table called Books. The Books table has four columns: id, name, category, and price.

CREATE DATABASE BookStore;
GO

USE BookStore;

CREATE TABLE Books (
    id INT,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price INT NOT NULL
);

INSERT INTO Books VALUES
    (1, 'Book1', 'Cat1', 1800),
    (2, 'Book2', 'Cat2', 1500),
    (3, 'Book3', 'Cat3', 2000),
    (4, 'Book4', 'Cat4', 1300),
    (5, 'Book5', 'Cat5', 1500),
    (6, 'Book6', 'Cat6', 5000),
    (7, 'Book7', 'Cat7', 8000),
    (8, 'Book8', 'Cat8', 5000),
    (9, 'Book9', 'Cat9', 5400),
    (10, 'Book10', 'Cat10', 3200);

Now, let’s look at an example where executing multiple queries without using transactions can lead to problems. Consider the following script:

INSERT INTO Books VALUES (15, 'Book15', 'Cat5', 2000);
UPDATE Books SET price = '25 Hundred' WHERE id = 15;
DELETE FROM Books WHERE id = 15;

In the above script, we execute three queries. The first query inserts a new record in the Books table, the second query updates the price of a book, and the third query deletes a record. However, if the second query fails, the first query still executes. This can lead to inconsistent data in the database.

To avoid such issues, we can use transactions to automatically rollback SQL queries. Let’s see how transactions can be used to rollback SQL queries:

BEGIN TRANSACTION;
INSERT INTO Books VALUES (20, 'Book15', 'Cat5', 2000);
UPDATE Books SET price = '25 Hundred' WHERE id = 20;
DELETE FROM Books WHERE id = 20;
COMMIT TRANSACTION;

In the above script, we start a transaction using the BEGIN TRANSACTION statement. We then execute the same three SQL queries as before, but this time inside a transaction. If any of the queries fail, all the previously executed queries will be rolled back automatically.

However, there may be cases where you want to manually rollback a transaction based on certain conditions. For example, you may want to rollback a transaction that inserts a record if a record with the same name already exists. In such cases, you can use the rollback SQL statement. Here’s an example:

DECLARE @BookCount INT;

BEGIN TRANSACTION AddBook;
INSERT INTO Books VALUES (20, 'Book15', 'Cat5', 2000);
SELECT @BookCount = COUNT(*) FROM Books WHERE name = 'Book15';

IF @BookCount > 1
BEGIN
    ROLLBACK TRANSACTION AddBook;
    PRINT 'A book with the same name already exists';
END
ELSE
BEGIN
    COMMIT TRANSACTION AddBook;
    PRINT 'New book added successfully';
END

In the above script, we declare a variable @BookCount and create a named transaction called AddBook. Inside the transaction, we insert a book record and then check if a book with the same name already exists. If it does, we manually rollback the transaction using the rollback SQL statement. Otherwise, we commit the transaction.

By understanding how to use transactions and the rollback SQL statement, you can ensure data consistency and handle errors effectively in SQL Server.

Conclusion:

In this article, we discussed the rollback SQL statement in SQL Server. We learned how transactions can be used to automatically rollback SQL queries and how to manually rollback a transaction based on certain conditions. By using transactions and the rollback SQL statement, you can maintain data integrity and handle errors efficiently in your SQL Server database.