This is a demonstration of Transactions and roll back in a stored procedure. Let’s create a table and try it.

— Create TestTable

CREATE TABLE tbl_TESTA (ID INT);

CREATE TABLE tbl_TESTB (ID INT);

CREATE TABLE tbl_TESTC (ID INT);

CREATE TABLE tbl_TESTD (ID INT);

GO

If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors.

CREATE PROCEDURE sp_tester

AS

BEGIN TRAN

INSERT INTO tbl_TESTA (ID) VALUES (0)

INSERT INTO tbl_TESTB (ID) VALUES (‘a’)

INSERT INTO tbl_TESTC (ID) VALUES (2)

INSERT INTO tbl_TESTD (ID) VALUES (3)

COMMIT

— Execute Stored Procedure

— Stored Procedure will error out

EXEC sp_tester

GO

— Check the Values in Table

SELECT * FROM tbl_TESTA;

SELECT * FROM tbl_TESTB;

SELECT * FROM tbl_TESTC;

SELECT * FROM tbl_TESTD;

GO

E.g. if you are inserting three records into three tables with wrong values for a second table, it should roll back complete transactions.

You may also add BEGIN TRY and  BEGIN CATCH in above statement. The statement should roll back complete transactions if there are encounters any errors.

CREATE PROCEDURE sp_tester

AS

BEGIN TRY

BEGIN TRAN

INSERT INTO tbl_TESTA (ID) VALUES (0)

INSERT INTO tbl_TESTB (ID) VALUES (‘a’)

INSERT INTO tbl_TESTC (ID) VALUES (2)

INSERT INTO tbl_TESTD (ID) VALUES (3)

PRINT ‘TRANSFER SUCCESSFUL.’

COMMIT

END TRY

BEGIN CATCH 

ROLLBACK

PRINT ‘TRANSACTION ROLLBACK.’

END CATCH

— Execute Stored Procedure

— Stored Procedure will error out

EXEC sp_tester

GO

— Check the Values in Table

 SELECT * FROM tbl_TESTA;

 SELECT * FROM tbl_TESTB;

 SELECT * FROM tbl_TESTC;

 SELECT * FROM tbl_TESTD;

 GO

Note: To test successful translation, change “INSERT INTO tbl_TESTB (ID) VALUES (1)” From “INSERT INTO tbl_TESTB (ID) VALUES (‘a’)” and run it.

CREATE PROCEDURE sp_tester

AS

BEGIN TRY

BEGIN TRAN

INSERT INTO tbl_TESTA (ID) VALUES (0)

INSERT INTO tbl_TESTB (ID) VALUES (1)

INSERT INTO tbl_TESTC (ID) VALUES (2)

INSERT INTO tbl_TESTD (ID) VALUES (3)

PRINT ‘TRANSFER SUCCESSFUL.’

COMMIT

END TRY

BEGIN CATCH

ROLLBACK

PRINT ‘TRANSACTION ROLLBACK.’

END CATCH

— Execute Stored Procedure

— Stored Procedure will error out

EXEC sp_tester

GO

— Check the Values in Table

SELECT * FROM tbl_TESTA;

SELECT * FROM tbl_TESTB;

SELECT * FROM tbl_TESTC;

SELECT * FROM tbl_TESTD;

GO