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
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
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…