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

Answer 1

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

Answer 2

IF i remove the "begin transaction" statenment the it does not any error if the child data exists

Answer 3

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

Answer 4

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

Answer 5

Thanks Ranganath it works fine

Answer 6

Thanks Tibork I am going through the article it is very helpful

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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.