SQL SERVER – Stored Procedure and Transactions
June 2, 2010 by pinaldave
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 TablesCREATE TABLE TABLE1 (ID INT);CREATE TABLE TABLE2 (ID INT);CREATE TABLE TABLE3 (ID INT);GO-- Create SPCREATE PROCEDURE TestSPAS
INSERT INTO TABLE1 (ID)VALUES (1)INSERT INTO TABLE2 (ID)VALUES ('a')INSERT INTO TABLE3 (ID)VALUES (3)GO-- Execute SP
-- SP will error outEXEC TestSP
GO-- Check the Values in TableSELECT *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 TestSPTranAS
BEGIN TRAN
INSERT INTO TABLE1 (ID)VALUES (11)INSERT INTO TABLE2 (ID)VALUES ('b')INSERT INTO TABLE3 (ID)VALUES (33)COMMITGO-- Execute SPEXEC TestSPTran
GO-- Check the Values in TablesSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO-- Clean upDROP TABLE Table1DROP TABLE Table2DROP 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)
No comments:
Post a Comment