SQL SERVER – Stored Procedure and Transactions

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)

About these ads

60 thoughts on “SQL SERVER – Stored Procedure and Transactions

  1. 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

  2. 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,);

  3. 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

  4. How to create stored procedure ?
    how it is executed ?
    there is required any table for creating stored procedure
    please help me.

    Thanks,
    kunal

  5. 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

  6. 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.

  7. 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’

  8. 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

  9. 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

  10. 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

  11. 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,

  12. 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

  13. Pingback: SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31 « SQL Server Journey with SQL Authority

  14. what will happen if transaction failed you have not written rollback will it be managed automatically please confirm

  15. 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

  16. 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 :)

  17. 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 :)

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. can u elaborate the topic ” I do not use Transactions in SQL as I use Stored Procedure“ pls i didnt get any clarification

  25. 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.

  26. 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

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s