I just overheard the following statement – “I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb

GO

-- Create 3 Test Tables

CREATE TABLE TABLE1 (ID INT);

CREATE TABLE TABLE2 (ID INT);

CREATE TABLE TABLE3 (ID INT);

GO

-- Create SP

CREATE PROCEDURE TestSP

AS

INSERT INTO TABLE1 (ID)

VALUES (1)

INSERT INTO TABLE2 (ID)

VALUES ('a')

INSERT INTO TABLE3 (ID)

VALUES (3)

GO

-- Execute SP

-- SP will error out

EXEC TestSP

GO

-- Check the Values in Table

SELECT *

FROM TABLE1;

SELECT *

FROM TABLE2;

SELECT *

FROM TABLE3;

GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.

CREATE PROCEDURE TestSPTran

AS

BEGIN TRAN

INSERT INTO TABLE1 (ID)

VALUES (11)

INSERT INTO TABLE2 (ID)

VALUES ('b')

INSERT INTO TABLE3 (ID)

VALUES (33)

COMMIT

GO

-- Execute SP

EXEC TestSPTran

GO

-- Check the Values in Tables

SELECT *

FROM TABLE1;

SELECT *

FROM TABLE2;

SELECT *

FROM TABLE3;

GO

-- Clean up

DROP TABLE Table1

DROP TABLE Table2

DROP TABLE Table3

GO

In this case, there will be no entries in any part of the table. What is your opinion about this blog post? Please leave your comments about it here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)