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
i want to use transaction for roll back in asp.net, because i am inserting multiple query in sql server 2008. please help me with examples
This is very helpful.
Excellent Blog… well explained, Thanks Pinal
Give me one example on INOUT parameters in sql……….
can you explain why we write table name after transcation in Sp
Can I use two begin transaction at the same procedure?
hi, I would like to ask is it possible to rollback a 1st procedure if 2nd procedure fails. Both the procedure are called separately from VB.Net. One enter the data in the master table and the other enters the data details table.
Massum – You need to run both in single transaction.
I have a naive question, should not the 3rd insert command work? I mean, I am trying to understand the logic behind the execution. When an error is encountered, the subsequent statements are not executed; am I correct Pinal?
Thanks a bunch for this short, wonderful explanation.
Asif – the logic is simple. Some errors batch terminate and some errors are statement termination. That is the reason why the errors like Conversions or Divide by Zero will terminate as soon as the error is encountered.
I have a question, I have update a stored procedure but after some days i want ot rollback previously executed sp as in nested way. how to solve this
There is no version maintenance done by SQL Server. Once a stored procedure is alter’ed then there is no way to find previous version. That’s why there are softwares for version control like Visual Studio.
Why doesn’t SQL Server rollback the identity_insert state?
Example :
We currently testing production script , where our identity insert column generating after rollback transaction. Below our trial script which give brief idea about it.
We created Temp table with Identity Insertion . Transaction stated as Begin And Rollback transaction.
But originally data not inserted but , when we execute next time , new id generate,
How we can use identity_insert with rollback only with single Identity ID ?
Code –
drop table #T
create table #T
(
id bigint identity(1,1),
ads bigint
)
begin transaction
insert into #T
(ads) values(1)
select *from #T
Rollback transaction
select *from #T
Hi Pinal,
I would like know that, while we create a new Stored procedure from the SQL Server management Studio. Default below header is coming.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:
— Create date:
— Description:
— =============================================
CREATE PROCEDURE
Now instead of this default header text I want change text in this default header that mean default I want to add Author name, Current date and few other custom fields in header comment.
Is there any way to customize header comment in SQL Management Studio?
Awaiting for your response.
Thanks in advance.
Regards
Jignesh Patel
use template.. https://docs.microsoft.com/en-us/sql/ssms/template/template-explorer?view=sql-server-2017
Hi Pinal,
Thanks for providing template link.
But is there any way that in template or any place where I can change stored procedure default header and while creating new stored procedure from the SQL Management studio default stored procedure header come.
Thanks & Regards
Jignesh Patel
In stored procedures why transactions are used?
What happens when we use a transaction inside a Stored Procedure which itself calls other stored procedures inside it. If the procedure is rolled back then what will happen to the stored procedure which has been already run inside the procedures.
Do they roll back or not? Plz help
Hello Pinal,
Are there performance implications of having just select in transactions. If all the stored procedure does is run a select query is it useful to wrap it in BEGIN and END transaction code. What are the advantages/disadvantages of doing this.? the database that i have seen where the select code is wrapped in Transaction has isolation level READ COMMITTED