SQL Server Performance

Transaction handling in Nested Stored Procedures

Discussion in 'General DBA Questions' started by sonnysingh, Jun 12, 2007.

  1. sonnysingh Member

    Hi All

    I have used nested stored Procedures on 3 level down. I am using Begin Tran..Commit Tran in each of SPs. But problem is if transaction is committed in 3rd level or 2nd level SP and error occurred in 1st level SP then the committed transactions in 2nd or 3rd level SP must be rollback. And it is not happening rather data was saved by committed Tran in 2nd or 3rd level SPs.

    HHow do I handle this situation?

    Thank in Advance
  2. masri999 New Member

    Following should work

    Create procedure usp1
    as

    declare @rtnvalue int
    begin tran

    exec @rtnvalue = usp2
    if rtnvalue <> 0 goto error_rtn

    insert into table1 values ( 1) ...
    if @@error <> 0 goto error_rtn

    if @@trancount > 0
    commit
    return 0

    error_rtn:
    if @@trancount > 0
    rollback
    return -1

    go

    Create procedure usp2
    as

    declare @rtnvalue int
    begin tran

    exec @rtnvalue = usp3
    if rtnvalue <> 0 goto error_rtn

    insert into table2 values ( 1) ...
    if @@error <> 0 goto error_rtn

    if @@trancount > 0
    commit
    return 0

    error_rtn:
    if @@trancount > 0
    rollback
    return -1
    go

    Create procedure usp3
    as

    declare @rtnvalue int
    begin tran


    insert into table3 values ( 1) ...
    if @@error <> 0 goto error_rtn

    if @@trancount > 0
    commit
    return 0

    error_rtn:
    if @@trancount > 0
    rollback
    return -1






    M A Srinivas
  3. satya Moderator

  4. sonnysingh Member

    masri999.. I am using nested procedures...calling another procedure from 1st procedure and then again calling another procedure from second called procedure.

    staya.. This is for SQL 2000...any example, refer, site??

    Thanks in advance
  5. satya Moderator

    Please adhere to forum policies and post in relevant sections only.

    Refer tohttp://www.sql-server-performance.com/ak_exception_handling.asp &http://www.sommarskog.se/error-handling-II.html

    Fyi in SQL 2k:
    The error handling behavior of SQL Server in nested procedures is different from that of the nested TSQL blocks.

    For example, a stored procedure, named PROC1, is called inside another stored procedure, named PROC2. The execution process of stored procedure PROC2 does not end if a fatal error occurs in PROC1. However, the execution of a stored procedure in PROC2 will end.
    If any fatal error occurs in PROC1, PROC2 continues the execution.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. sonnysingh Member

    thanks satya

    refer to your reply

    quote:
    For example, a stored procedure, named PROC1, is called inside another stored procedure, named PROC2. The execution process of stored procedure PROC2 does not end if a fatal error occurs in PROC1. However, the execution of a stored procedure in PROC2 will end.
    If any fatal error occurs in PROC1, PROC2 continues the execution.

    what happen if transaction are committed in the PROC2.. are those saved in the database. if yes then how can avoid/handle this situation?

    Thanks
    sonny
  7. satya Moderator

    You shoudl control that by trapping errors with a conditional statements to abort the PROC2.
    http://www.code-magazine.com/Article.aspx?quickid=0305111 fyi in addition to the links above.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page