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)
Hi There!
It’s very interesting and logical too.
Well! SP or any other DB object serves as a medium to execute several statements. So, it doesn’t make any sense in co-relating them with trasnaction. Transaction takes place in it’s own way.
Hope it helps!
Regards,
Umesh Bhavsar
Nice one. It’s simply explained. Thank You!
nice one for the ppl who thinks sp handle transaction well without using begin tran or commit
One more thing. First read this:
http://blog.sqlauthority.com/2010/05/21/sql-server-simple-example-of-snapshot-isolation-reduce%C2%A0the%C2%A0blocking%C2%A0transactions/
Now if you are using snapshot isolation but don’t start up transaction, even if you use stored procedure, you don’t get any benefits from snapshot. You need to start transaction (with isolation level snapshot) to use snapshot.
In order to assure that my procedures are called inside transactions I’ve make habit of writing following in the head of every procedure:
IF @@TRANCOUNT = 0 RAISERROR (‘Must be called within transaction!’, 16, 1,);
Oh. There’s a typo in that T/SQL clause. Remove the last comma to get it working.
Good article. when designing OLTP systems it would be always good to use transactions.
Just a quick question how do you maintain the transaction if you are using nested stored procedure?
hi,
it’s cleared that SP doesn’t maintain transaction in stored procedure.
but i have a question here
( I faced the same problem described following)
while using the transaction in SP
in the same scenario when the error occurred while inserting into table2 the table2 gets locked for the 5 to 10 minutes.
during this time m unable to retrieve the data from the table
table2.
it goes stuck into a long process and end with timeout message.
please can I have any solution for this?/
I think you need to catch the error, rollback transaction and rethrow the error if needed. Like this.
BEGIN TRANSACTION tran
BEGIN TRY
– Do your stuff here
– This throws an error, just for example
SELECT 1/0
COMMIT TRANSACTION tran
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION tran
– Rethrow
DECLARE @errmsg NVARCHAR(4000)
DECLARE @errseverity INT
DECLARE @errstate INT
SELECT @errmsg = ERROR_MESSAGE(), @errseverity = ERROR_SEVERITY(), @errstate = ERROR_STATE()
RAISERROR(@erromsg, @errseverity, @errstate)
END CATCH
How to create stored procedure ?
how it is executed ?
there is required any table for creating stored procedure
please help me.
Thanks,
kunal
First read about Stored Procedures in SQL Server help file to get an initial idea
CREATE PROC [dbo].[SP_WebGym_DashBoard_MonthlyCollection]
(
@FitnessCenterID INT ,
@BranchID INT
)
AS
BEGIN
CREATE TABLE #TempFeesPending
(
MemberFeesHeaderID INT ,
PendingAmount DECIMAL(18,2)
)
INSERT INTO #TempFeesPending
(
MemberFeesHeaderID ,
PendingAmount
)
SELECT MemberFeesHeaderID ,
SUM(ISNULL(SubTotal,0)) AS ‘PendingAmount’
FROM DBO.Tbl_WebGym_MemberFees_Detail
WHERE Delete_Flag = 0
AND BranchDetailID = @BranchID
AND FeesTypeID = 6
AND CONVERT(DATETIME,RecordInsertedDate ,103) BETWEEN CONVERT(DATETIME,(CONVERT(VARCHAR(4),YEAR(GETDATE()))+’-'+CONVERT(VARCHAR(2),MONTH(GETDATE()))+ ‘-01′)) AND GETDATE()
GROUP BY MemberFeesHeaderID
SELECT DATEPART(month, GETDATE()) AS ‘Mon’ ,
SUM(ISNULL(OverallTotal,0))-SUM(ISNULL(PendingAmount,0)) AS ‘Amount’
FROM DBO.Tbl_WebGym_MemberFees_Header FHR (NOLOCK)
LEFT JOIN #TempFeesPending FDL (NOLOCK)
ON FDL.MemberFeesHeaderID = FHR.MemberFeesHeaderID
WHERE Delete_Flag = 0
AND BranchDetailID = @BranchID
AND MONTH(RecordInsertedDate) = MONTH(GETDATE())
AND YEAR(RecordInsertedDate) = YEAR(GETDATE())
UNION
SELECT DATEPART(month, DATEADD(MONTH,-1,GETDATE())) AS ‘Mon’ ,
SUM(ISNULL(OverallTotal,0))-SUM(ISNULL(PendingAmount,0)) AS ‘Amount’
FROM DBO.Tbl_WebGym_MemberFees_Header FHR (NOLOCK)
LEFT JOIN #TempFeesPending FDL (NOLOCK)
ON FDL.MemberFeesHeaderID = FHR.MemberFeesHeaderID
WHERE Delete_Flag = 0
AND BranchDetailID = @BranchID
AND MONTH(RecordInsertedDate) = MONTH(DATEADD(MONTH,-1,GETDATE()))
AND YEAR(RecordInsertedDate) = YEAR(DATEADD(MONTH,-1,GETDATE()))
UNION
SELECT DATEPART(month, DATEADD(MONTH,-2,GETDATE())) AS ‘Mon’ ,
SUM(ISNULL(OverallTotal,0))-SUM(ISNULL(PendingAmount,0)) AS ‘Amount’
FROM DBO.Tbl_WebGym_MemberFees_Header FHR (NOLOCK)
LEFT JOIN #TempFeesPending FDL (NOLOCK)
ON FDL.MemberFeesHeaderID = FHR.MemberFeesHeaderID
WHERE Delete_Flag = 0
AND BranchDetailID = @BranchID
AND MONTH(RecordInsertedDate) = MONTH(DATEADD(MONTH,-2,GETDATE()))
AND YEAR(RecordInsertedDate) = YEAR(DATEADD(MONTH,-2,GETDATE()))
DROP TABLE #TempFeesPending
END
==========================
i want to know the last 12 month
For eg;
Currently july 2010 i want to take up the records from july 2009. I need a solution
Hello
I have a scenario where one stored procedure updates a table and then calls another stored procedure to update related tables.
How should i go about implementing transactions in this scenario.?
Start transaction, call procedure 1, which then subsequently calls procedure 2. Finally commit transaction. Everything is done inside the single transaction.
You can make sure the transaction is used by adding following line at the head of your procedures:
IF @@TRANCOUNT = 0 RAISERROR (‘Must be called within transaction!’, 16, 1);
If transaction is not enable error is thrown.
HOW TO PASS VALUES TO THE TEMPERARY TABLE COLUNS
create proc usp_GetMidDayMealForSchool1
–(
–@SchoolId as int,@Dates as datetime,@EducationYearId as int,@StandardId as int)
as
begin
create table MidDayMealForSchool
(
TodayMeal nvarchar(50),
PresentStudent nvarchar(50),
NoOfPlates nvarchar(50),
rice nvarchar(50),
MugDaal nvarchar(50),
ToorDaal nvarchar(50),
MasoorDaal nvarchar(50),
Gool nvarchar(50),
Sugar nvarchar(50),
Matki nvarchar(50),
Moog nvarchar(50),
Chavali nvarchar(50),
Chana nvarchar(50),
Vaatana nvarchar(50),
Mohari nvarchar(50),
Jeera nvarchar(50),
Haldi nvarchar(50),
Chilli_Powder nvarchar(50),
Garam_Masala nvarchar(50),
Soyabean_Oil nvarchar(50),
Besan nvarchar(50),
ProcessedSoyaBean nvarchar(50),
Salt nvarchar(50),
Vegetables nvarchar(50),
PetrolAndLabourExpenses nvarchar(50)
)
declare @TodayMeal nvarchar(50)
declare @NoOfPlates nvarchar(50)
declare @rice nvarchar(50)
declare @MugDaal nvarchar(50)
declare @ToorDaal nvarchar(50)
declare @MasoorDaal nvarchar(50)
declare @Gool nvarchar(50)
declare @Sugar nvarchar(50)
declare @Matki nvarchar(50)
declare @Moog nvarchar(50)
declare @Chavali nvarchar(50)
declare @Chana nvarchar(50)
declare @Vaatana nvarchar(50)
declare @Mohari nvarchar(50)
declare @Jeera nvarchar(50)
declare @Haldi nvarchar(50)
declare @Chilli_Powder nvarchar(50)
declare @Garam_Masala nvarchar(50)
declare @Soyabean_Oil nvarchar(50)
declare @Besan nvarchar(50) select * from Admission_Mst
declare @ProcessedSoyaBean nvarchar(50)
declare @Salt nvarchar(50) select * from StandardStudent
declare @Vegetables nvarchar(50)
declare @PetrolAndLabourExpenses nvarchar(50) select * from Food_Mst
select @TodayMeal=Count(*) from Food_Mst f, Admission_Mst A,StandardStudent S where A.AdmissionId=S.AdmissionId and A.IsLeft!=1 and A.Deactive=0
and S.EducationYearId=@EducationYearId and S.StandardId=@StandardId and A.SchoolId=@SchoolId and f.FoodItemName=’Rice’
select @TodayMeal=Count(*) from Food_Mst f, Admission_Mst A,StandardStudent S where A.AdmissionId=S.AdmissionId and A.IsLeft!=1 and A.Deactive=0
and S.EducationYearId=@EducationYearId and S.StandardId=@StandardId and A.SchoolId=@SchoolId and f.FoodItemName=’MugDaal’
very well explained.
hi guys….
its an intresting stuf i got now and i need ur help for that..
i have created table in this form:
create table emp
(e_id nvarchar(50) primary key,
ename varchar(50))
e_id | ename
a1 x
a2 y
a.. n..
a15 z
select max(e_id) from emp
output :
a9
you can notice that above out put is wrong
the answer as to be a15….
plz let me now where im doing wrong
thanks in advance
Hi Brijesh,
Problem:
You will get an insight by running this query:
SELECT e_id from Emp ORDER BY e_id
You will see that e_id is being sorted by the character’s sequence and not like numbers. So, a10 will be followed by a1.
So, as per sort logic, a9 is the max value.
Solution:
SELECT LEFT(e_id, 1) + CAST(MAX(CAST(SUBSTRING(e_id, 2, LEN(e_id)) AS INT)) AS NVARCHAR(50)) FROM Emp GROUP BY LEFT(e_id, 1)
Please note that this query is based on the first character of the e_id column values. If let say someone inserts another 15 records starting from b1 , x to b15, z then the output of this query will be of 2 rows:
a15
b15
Warm Regards,
Umesh Bhavsar
very well……
thanks Bhavsar
Nice….
Could you please suggest alternatives to stored procedures?
Thanks in advance
Chithu das
In what aspect, do you want an alternate to the procedure?
good afternoon pinal sir and madhivanan sir
i knew agg function,view,create backeup and restore,dml and ddl ,select query, in ,and or,primary concept and foreigh key concept and index or clustered index,i dont know stored procdure and triggers
i have read many articles but no profit
i couldnot understand stored procdure and triggers and udf function
aim
my aim is become a database developer
plz help this query
SQL Server help file has informations about procedures, triggers,etc. Have a look at them and if you dont understand ask us
Please help me on the below issue,
We have application running and now few enhancements are taking place.In one table we are stroring values like(123.345 , 2.123…..etc) as varchar.It was designed like that (Now we cant change the table) .
we are writing a store procedure to fetch values between ranges like (1.22 to 1.55)… while comparing we are converting into decimal from varchar type (we tried with real also) and we are getting timeout error in the apllication. SP is running is SQL Management studio , but it is taking time.
Please suggest how we can proceed.
– Shall we create new coloumn same as this by changing it to real or decimal and a trigger to update that coloumn
everytime
or
– shall we create a temp table
please suggest
Hi,
You don’t need to go for a temp table or additional column.
Infact you don need any conversion of datatype as well.
Write a simple query in your stored procedure
SELECT * FROM [Table Name] WHERE [Column Name] BETWEEN @Min_Value AND @Max_Value.
This will give you the desired result even with VARCHAR as its Datatype.
Regards,
good reply
I very thankful to u because u r remove our problem about IT
I already working RDBMS of INFORMIX but my new job is
sqlserver-2008
so please i request to u please guide about valueable book of
sqlserver2008 and also guide me about joins,stored procedure
writting style in sqlserver2008, cursor writting in sqlserver2008
i am very glad to visit u r address side
Please guide about MS SQLSERVER and stored procedure
This is very generic question. Be specific on what you want to ask for
Nice Article
Coool..!!!
Nice Article…
[...] SQL Server Interview Questions and Answers ISBN: 1466405643 Page#99 Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time Recompile Stored Procedure at Run Time Encrypted Stored Procedure and Activity Monitor Stored Procedure and Transactions [...]
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?
http://blogs.msdn.com/b/florinlazar/archive/2005/10/04/476775.aspx
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
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.
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