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 (https://blog.sqlauthority.com)
75 Comments. Leave new
what will happen if transaction failed you have not written rollback will it be managed automatically please confirm
But if i change the Store to take 3 parameters i don’t see any record in these table the result will be the same with the transaction can you just explain for me :
CREATE PROC TESTSPParam (@t1 int,@t2 int, @t3 int)
as
INSERT INTO TABLE1 VALUES (@t1)
INSERT INTO TABLE2 VALUES (@t2)
INSERT INTO TABLE3 VALUES (@t3)
GO
EXEC TESTSPParam 1,’A’,3
GO
What do you think of this article which basically says to use transactions whenever in doubt?
https://blogs.msdn.microsoft.com/florinlazar/2005/10/04/why-and-when-to-use-transactions/
Personally I think that may be a bit excessive, but I’m not experienced enough at this point to say for certain. At the least, I would expect that if you were going to use them whenever in doubt, you certainly would want to carefully consider their scope and precisely what ought to be wrapped and what not, especially in long stored procedures. You wouldn’t want to wrap the whole thing unless absolutely necessary, thereby locking up and slowing down the system as well as increasing the chance of code not executing when it should because of some missed bug. Am I wrong here?
Thanks, and love your blog, I refer to it all the time :)
Hi every one,
I have a sql server 2000 db and a java application that will access just two stored procedures in parallel ( 20 thread that will handle the business ).
I note that the first thread will acquire the table to insert it data, so the other threads will wait until it finish.
The SP will insert data in two tables other code is just select and some
decisions.
Can I use the BEGIN TRANSACTION & COMMIT TRANSACTION around the
insert statements only.
Thanks in advance :)
Great way to explain!
if you do not write the ROLLBACK part your explanation is WRONG !
CREATE PROCEDURE TestSPTran
AS
BEGIN TRAN
INSERT INTO TABLE1 (ID)
VALUES (11)
IF @@ERROR 0
BEGIN
— Rollback the transaction
ROLLBACK
RETURN
END
INSERT INTO TABLE2 (ID)
VALUES (‘b’)
IF @@ERROR 0
BEGIN
— Rollback the transaction
ROLLBACK
RETURN
END
INSERT INTO TABLE3 (ID)
VALUES (33)
IF @@ERROR 0
BEGIN
— Rollback the transaction
ROLLBACK
RETURN
END
COMMIT
Hey, I have a Stored Procedure including a transaction that implements many different things and takes a while to perform. Before, this stored procedure didn’t implement the transaction and only saved a log on a database table that indicated which the step of the procedure I was in. But with begin transaction, this can’t help me anymore because it doesn’t save anything until it does commit. Do you have any clues about how should I implement this? I came up with some ideas but I can’t find a solution:
– Be able to use INSERT but from outside the ingoing transaction
– Generate events so that SQLServer can register them somewhere.
Thanks in advance,
Esteban
Hello,
I know this is an old post, but have you found a solution for this?
Thank you,
Iana
hi
very interesting.post.
i also wanna implement transaction my sp ,
My SP is–
CREATE proc pay_transc_deletemonthlytransc 07,’INT01′ ,2012
(
@smonth decimal(2,0),
@compcode varchar(5),
@syear decimal(4,0)
)
as
delete from pay_monthly_transaction where month(salary_date) = @smonth and year(salary_date) = @syear and emp_code in (select emp_code from pay_employee_master where company_code =@compcode)
delete from pay_monthly_transaction_detail where month(salary_date) = @smonth and year(salary_date) = @syear and emp_code in (select emp_code from pay_employee_master where company_code = @compcode)
delete from pay_advance_detail where month(saldate) = @smonth and year(saldate) = @syear and emp_code in (select emp_code from pay_employee_master where company_code = @compcode)
delete from pay_salary_slip where month = @smonth and year = @syear and emp_code in (select emp_code from pay_employee_master where company_code = @compcode)
here aim deleting Record from 3 table. i wanna Immplement Transction
Thanks
HI Mr.Pinal
Firstly I would like to thank you for coming up with such a useful blog.
And I came to hear about sql server from one of my friend , and since then am in love with it, I started learning sql server by my own and the very first book I happen to read was “SQL Server
2005 DBA Street Smarts”, for simple reason that it has each and every step explaining how to go about it. I want to be strong in the following concepts
– stored procedures
-functions
-triggers
-index
am willing to go more towards DBA but my perception is we need to be familiar with dev as well, so kindly help me in going ahead and learn more effectively, I have set up sql server 2008 evaluation with SSIS, SSAS, SSRS.
Regards
Raghav Kotla
Nice Explain Keep it up
Excellent Blog Pinal!!!!
I have a question…
What happend with a transaction, if it’s interruped by a timout?
Excellect!!
Hi
If am inserting records in a table and using it to update other table with in same stored procedure between transaction,it is taking very long time because of millions of records but if I commit after first insert and start new transaction for update and then commit again then it is much much faster. I want to ask, if this type of coding is acceptable?
BEGIN TRANSACTION
INSERT INTO TABLE
COMMIT
BEGIN TRANSACTION
UPDATE TABLE2 FROM TABLE
COMMIT
—————————————————-
Thanks
Even after wrapping up the code inside Begin Tran and commit, the entry for table 1 is still there, please check ur code.
Excellent…Please also contiue with the examples for Triggers, Cursors…
Hi Dave, i have a question, I have a functionality where i am calling main store procedure which is calling 10 other stored procedures, they way i designed is , Main stored procedure is code is enclosed between Begin and End Transaction and all the child proc i am usnig Begin Try / catch block and if something errors out i am raising an error, is this desgin efficient .. Let me know .. thanks
thanks Pinal
can u elaborate the topic ” I do not use Transactions in SQL as I use Stored Procedure“ pls i didnt get any clarification
Hi Pinal,
I tried the above code explained by you to create SP by using begin tran and commit. But there is no much difference.Iam new to SQL iam still in learning stage.Kindly recheck the code and explain.
Thanks.