none
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

    Question

  • ALTER proc [dbo].[uspDeleteChannel]
    @ChannelId bigint
    as
    begin
        declare @value int,@retval int
        set @retval=0

        begin transaction
        if((select count(1) from tblchannelBranch where ChannelId=@ChannelId)>0)
        begin
        return @retval
        end
        else
        begin
        update [tblChannel] set [status]='I' where [ChannelId]=@channelId
        set @retval=1
        end
       
        if(@@error=0)
        begin
            commit transaction
            return @retval
        end
        else
        begin
            rollback transaction
        end



    Hi My senario is to update the status of Channel rows, IF no child date(channelbranch) data present
    If child data exists no need to update the status of Channel
    Tuesday, June 16, 2009 8:44 AM

Answers

  • Did you really managed to read those two articles in 4 minutes? I posted and recommended you to read them because this will be benefifial to you. Again, your problem is that you enter the procedurew with an open transaction. SQL Server will produce an error if you exit the procedure with a different trancount compared to when you entered the procedure. Nested transaction (or the lackl of, depending on how you want to look at it) is a slightly complex topic which is why you want to read up on the topic first and then think through your error handling and transaction handling strategy based on how SQL server behaves.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by anjigvr Tuesday, June 16, 2009 9:15 AM
    Tuesday, June 16, 2009 9:09 AM

All replies

  • Your problem is that you enter this proc with an open transaction and apparently exit after doing rollback. Transaction handling is very related to error handling. I strongly encourage you to take a couple of hours reading the error handling articles found here: http://www.sommarskog.se/

    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Gagan.K Saturday, January 01, 2011 10:47 PM
    Tuesday, June 16, 2009 8:59 AM
  • IF i remove the "begin transaction" statenment the it does not any error if the child data exists

    Tuesday, June 16, 2009 9:02 AM
  • hi try this

    ALTER proc [dbo].[uspDeleteChannel]
    @ChannelId bigint
    as
    begin
        declare @value int,@retval int
        

      
        if((select count(1) from tblchannelBranch where ChannelId=@ChannelId)>0)
        begin
        set @retval=0
        end
        else
        begin
      begin transaction
        update [tblChannel] set [status]='I' where [ChannelId]=@channelId
        set @retval=1
     if(@@error>0)
        begin
             rollback transaction
               end
        else 
            commit trans

        end
        
       
         return @retval
           
        end
    Tuesday, June 16, 2009 9:06 AM
  • Did you really managed to read those two articles in 4 minutes? I posted and recommended you to read them because this will be benefifial to you. Again, your problem is that you enter the procedurew with an open transaction. SQL Server will produce an error if you exit the procedure with a different trancount compared to when you entered the procedure. Nested transaction (or the lackl of, depending on how you want to look at it) is a slightly complex topic which is why you want to read up on the topic first and then think through your error handling and transaction handling strategy based on how SQL server behaves.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by anjigvr Tuesday, June 16, 2009 9:15 AM
    Tuesday, June 16, 2009 9:09 AM
  • Thanks Ranganath it works fine
    Tuesday, June 16, 2009 9:15 AM
  • Thanks Tibork I am going through the article it is very helpful
    Tuesday, June 16, 2009 9:16 AM
  • Good, anjivgr. :-)

    You are doing yourself a favour over time, even though it is a bit spending time up-front.

    Just a quick warning: Ranganath's suggestion had a potential weakness. There is nothing that probibits somebody inserting a new row after your existence check but before your insert. tO probibit that you need an apropriate isolation level and possibly also a suitable locking hint.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 16, 2009 9:19 AM
  • May be you do not need an explicit transaction here.


    ALTER proc [dbo].[uspDeleteChannel]
    @ChannelId bigint,
    @RowsAffected int output
    as
    set nocount on;

    declare @error int;

    update
        [tblChannel]
    set
        [status]='I'
    where
        [ChannelId]=@channelId
        and exists (select * from tblchannelBranch where ChannelId=@ChannelId);

    select @error = @@error, @RowsAffected = @@rowcount;

    return @error;
    GO


    No matter if this works for you, reading Erland's articles about error handeling in T-SQL is worth to do it.


    AMB
    Tuesday, June 16, 2009 1:09 PM
  • Hi Tibor,

    Thanks for redirecting to such a nice article. It helped to resolve the issues i was facing.

    Once again thank you very much!

    GK

    Saturday, January 01, 2011 10:54 PM
  • apologies for replying to an older post but im just wondering what article(s) you are referring to, as i have come across the same problems and would like to read these articles, thanks in advance

    Thursday, February 23, 2012 4:40 PM
  • I suspect it's this one http://www.sommarskog.se/error_handling_2005.html with the links to 2 older articles.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 23, 2012 4:44 PM
  • You have RETURN command in first IF without COMMIT TRAN or ROLLBACK TRAN before it. 

    RETURN command ends procedure so BEGIN TRAN doesnt have appropriate COMMIT or ROLLBACK in this branch.

    Wednesday, August 29, 2012 11:21 AM