Sunday, July 22, 2012

Stored Procedure and Transactions

Stored Procedure and Transactions

I simply overheard the subsequent statement – “I don't use Transactions in SQL as i exploit Stored Procedure“.

I simply realized that there are numerous misconceptions regarding this subject. Transactions has nothing to try and do with Stored Procedures. Let me demonstrate that with a straightforward example.

USE tempdb
GO
-- produce three check Tables
CREATE TABLE TABLE_1 (ID INT);
CREATE TABLE TABLE_2 (ID INT);
CREATE TABLE TABLE_3 (ID INT);
GO
-- produce SP
CREATE PROCEDURE TestSP
AS
INSERT INTO TABLE_1 (ID)
VALUES (1)
INSERT INTO TABLE_2 (ID)
VALUES ('a')
INSERT INTO TABLE_3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP can error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE_1;
SELECT *
FROM TABLE_2;
SELECT *
FROM TABLE_3;
GO

Now, the most purpose is: If Stored Procedure is transactional then, it ought to roll back complete transactions when it encounters any errors. Well, that doesn't happen during this case, that proves that Stored Procedure doesn't solely give simply the transactional feature to a batch of T-SQL.

Let’s see the result terribly quickly.

It is terribly clear that there have been entries in table1 that don't seem to be shown within the subsequent tables. If SP was transactional in terms of T-SQL question Batches, there would be no entries in any of the tables. If you would like 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 TABLE_1 (ID)
VALUES (11)
INSERT INTO TABLE_2 (ID)
VALUES ('b')
INSERT INTO TABLE_3 (ID)
VALUES (33)
COMMIT
GO
-- Execute SP
EXEC TestSPTran
GO
-- Check the Values in Tables
SELECT *
FROM TABLE_1;
SELECT *
FROM TABLE_2;
SELECT *
FROM TABLE_3;
GO

4 comments:

  1. I seriously consider this site needs much more attention.

    ReplyDelete
  2. Hi ! I wish to say that this post is awesome, nice written and include almost all important info. Id like to see more posts like this.

    ReplyDelete
  3. That is an extremely smart written article. I will be sure to bookmark it and return to learn extra of your useful information. Thank you for the post. I will certainly return.

    ReplyDelete