事务回滚 DEMO

因为有些事物回滚  查询的时候 可能查出来空值  

我们肯定不愿意把空值添加数据库里面

一般基本的是这么写

if (object_id('add_T_Disclose_DiscloseList', 'P') is not null)
    drop proc add_T_Disclose_DiscloseList
go

create proc add_T_Disclose_DiscloseList
    @user_id nvarchar(200),
    @title_id nvarchar(max),
    @jiaodi_id nvarchar(max),
    @jiedi_id nvarchar(max)
as
begin
declare @username nvarchar(200)
declare @orgcode nvarchar(200)
declare @jiaodi nvarchar(max)
declare @jiedi nvarchar(max)
declare @jiaojie_name nvarchar(200)
declare @jiaojie_count int
declare @jiaojie_id nvarchar(200)
declare @sex nvarchar(200)
declare @cardid nvarchar(200)
declare @addr nvarchar(200)
declare @pic nvarchar(200)

declare @i int
declare @main_id nvarchar(200)
    declare   @iErrorCount   int 
    set @iErrorCount = 0
    begin tran Tran1
        set @main_id='';
        set @jiaodi='';
        set @jiaojie_count=LEN(@jiaodi_id)-LEN(REPLACE(@jiaodi_id,',',''))+1;
        set @i=0;
        select @main_id=NEWID();
        select @username=_UserName,@orgcode=_OrgCode from T_E_Org_Employee  where _AutoID=@user_id
        if len(@username)<=0 or len(@orgcode)<=0
            begin
                set @iErrorCount=@iErrorCount+@@error+1
            end
            while @i<@jiaojie_count
                begin        
                    if @i>0
                        begin
                            set @jiaodi+=',';
                        end
                    select @jiaojie_name =name,@jiaojie_id=_AutoID,@sex=sex,@cardid=idcardno,@addr=address,@pic=photofilename from  T_Disclose_DiscloseManInfo where idcardno=SUBSTRING(@jiaodi_id,0,CHARINDEX(',',@jiaodi_id))
                    if len(@jiaojie_name)<=0 or len(@jiaodi_id)<=0 or len(@sex)<=0 or len(@cardid)<=0 or len(@addr)<=0
                        begin
                            set @iErrorCount=@iErrorCount+@@error+1
                            break;
                        end                    
                    set    @jiaodi_id=SUBSTRING(@jiaodi_id,charindex(',',@jiaodi_id)+1,LEN(@jiaodi_id))
                    set @jiaodi=@jiaodi+@jiaojie_name ;
                    insert into T_Disclose_DiscloseList_PersonInfo values(NEWID(),''+@username+'',''+@orgcode+'',GETDATE(),GETDATE(),0,''+@main_id+'','T_Disclose_DiscloseList',null,''+@jiaojie_id+'',''+@jiaojie_name+'',''+@sex+'',''+@cardid+'',''+@addr+'',''+@pic+'','是')
                    set @i+=1;
                    set @iErrorCount=@iErrorCount+@@error
                end
        set @i=0;
        set @jiedi='';
        set @jiaojie_count=LEN(@jiedi_id)-LEN(REPLACE(@jiedi_id,',',''))+1;
            while @i<@jiaojie_count
                begin
                    if @i>0
                        begin                            
                            set @jiedi+=',';
                            break;
                        end
                    select @jiaojie_name =name,@jiaojie_id=_AutoID,@sex=sex,@cardid=idcardno,@addr=address,@pic=photofilename from  T_Disclose_DiscloseManInfo where idcardno=SUBSTRING(@jiedi_id,0,CHARINDEX(',',@jiedi_id))
                    if len(@jiaojie_name)<=0 or len(@jiaodi_id)<=0 or len(@sex)<=0 or len(@cardid)<=0 or len(@addr)<=0
                        begin
                            set @iErrorCount=@iErrorCount+@@error+1
                            break;
                        end                    
                    set    @jiedi_id=SUBSTRING(@jiedi_id,charindex(',',@jiedi_id)+1,LEN(@jiedi_id))
                    set @jiedi=@jiedi+@jiaojie_name ;
                            insert into T_Disclose_DiscloseList_PersonInfo values(NEWID(),''+@username+'',''+@orgcode+'',GETDATE(),GETDATE(),0,''+@main_id+'','T_Disclose_DiscloseList',null,''+@jiaojie_id+'',''+@jiaojie_name+'',''+@sex+'',''+@cardid+'',''+@addr+'',''+@pic+'','否')
                    set @i+=1;
                    set @iErrorCount=@iErrorCount+@@error
                end    
            
        insert into T_Disclose_DiscloseList 
        (_AutoID,_UserName,_OrgCode,_CreateTime,_UpdateTime,_IsDel,DiscloseId,DiscloseTitle,DisclosePerson,DisclosePersonList,DiscloseDatetime)
         values
         (''+@main_id+'',''+@username+'',''+@orgcode+'',GETDATE(),GETDATE(),0,''+@title_id+'',''+(select  Ttile from T_Disclose_DiscloseInfo where _AutoID=''+@title_id+'')+'',''+@jiaodi+'',''+@jiedi+'',GETDATE())
    set @iErrorCount=@iErrorCount+@@error     
    if @iErrorCount>0
        begin  
            ROLLBACK TRAN Tran1  --回滚事务             
        end 
    else   
        begin   
            COMMIT TRAN Tran1  --执行事务
        end
 end
 

但是

执行第一遍没问题

第二遍

报错

EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1,当前

后来改了下

if (object_id('add_T_Disclose_DiscloseList', 'P') is not null)
    drop proc add_T_Disclose_DiscloseList
go

create proc add_T_Disclose_DiscloseList
    @user_id nvarchar(200),
    @title_id nvarchar(max),
    @jiaodi_id nvarchar(max),
    @jiedi_id nvarchar(max)
as
begin
declare @username nvarchar(200)
declare @orgcode nvarchar(200)
declare @jiaodi nvarchar(max)
declare @jiedi nvarchar(max)
declare @jiaojie_name nvarchar(200)
declare @jiaojie_count int
declare @jiaojie_id nvarchar(200)
declare @sex nvarchar(200)
declare @cardid nvarchar(200)
declare @addr nvarchar(200)
declare @pic nvarchar(200)

declare @i int
declare @main_id nvarchar(200)
    declare   @iErrorCount   int 
BEGIN TRAN 
    set @iErrorCount = 0

        set @main_id='';
        set @jiaodi='';
        set @jiaojie_count=LEN(@jiaodi_id)-LEN(REPLACE(@jiaodi_id,',',''))+1;
        set @i=0;
        select @main_id=NEWID();
        select @username=_UserName,@orgcode=_OrgCode from T_E_Org_Employee  where _AutoID=@user_id
        --if len(@username)<=0 or len(@orgcode)<=0
        --    begin
                
        --    end
            while @i<@jiaojie_count
                begin        
                    if @i>0
                        begin
                            set @jiaodi+=',';
                        end
                      IF ( NOT EXISTS (select* from  T_Disclose_DiscloseManInfo where idcardno=SUBSTRING(@jiaodi_id,0,CHARINDEX(',',@jiaodi_id))))
                        begin    
                        select @jiaojie_name =name,@jiaojie_id=_AutoID,@sex=sex,@cardid=idcardno,@addr=address,@pic=photofilename from  T_Disclose_DiscloseManInfo where idcardno=SUBSTRING(@jiaodi_id,0,CHARINDEX(',',@jiaodi_id))          
                            if (@jiaojie_name =null or @jiaojie_name ='null') or (@jiaodi_id is null) or (@sex is null) or (@cardid is null) or (@addr is null)
                                begin
                                  ROLLBACK TRAN ;  --要与 begintran 对应 
                                  RETURN 0 ; 
                                break;
                                end                    
                            set    @jiaodi_id=SUBSTRING(@jiaodi_id,charindex(',',@jiaodi_id)+1,LEN(@jiaodi_id))
                            set @jiaodi=@jiaodi+@jiaojie_name ;
                            insert into T_Disclose_DiscloseList_PersonInfo values(NEWID(),''+@username+'',''+@orgcode+'',GETDATE(),GETDATE(),0,''+@main_id+'','T_Disclose_DiscloseList',null,''+@jiaojie_id+'',''+@jiaojie_name+'',''+@sex+'',''+@cardid+'',''+@addr+'',''+@pic+'','是')
                            set @i+=1;
                              COMMIT TRAN ;    --要与 begintran 对应 
                              RETURN 1 ;
                        end
                    ELSE
                        begin
                           ROLLBACK TRAN ;  --要与 begintran 对应 
                           RETURN 0 ; 
                        end
                end
                
        set @i=0;
        set @jiedi='';
        set @jiaojie_count=LEN(@jiedi_id)-LEN(REPLACE(@jiedi_id,',',''))+1;
            while @i<@jiaojie_count
                begin
                    if @i>0
                        begin                            
                            set @jiedi+=',';
                            break;
                        end
                      IF ( NOT EXISTS (select* from  T_Disclose_DiscloseManInfo where idcardno=SUBSTRING(@jiedi_id,0,CHARINDEX(',',@jiedi_id))))
                        begin
                    select @jiaojie_name =name,@jiaojie_id=_AutoID,@sex=sex,@cardid=idcardno,@addr=address,@pic=photofilename from  T_Disclose_DiscloseManInfo where idcardno=SUBSTRING(@jiedi_id,0,CHARINDEX(',',@jiedi_id))
                        if (@jiaojie_name =null or @jiaojie_name ='null') or (@jiaodi_id is null) or (@sex is null) or (@cardid is null) or (@addr is null)
                            begin
                            ROLLBACK TRAN ;  --要与 begintran 对应 
                            RETURN 0 ; 
                            break;
                            end                    
                    set    @jiedi_id=SUBSTRING(@jiedi_id,charindex(',',@jiedi_id)+1,LEN(@jiedi_id))
                    set @jiedi=@jiedi+@jiaojie_name ;
                            insert into T_Disclose_DiscloseList_PersonInfo values(NEWID(),''+@username+'',''+@orgcode+'',GETDATE(),GETDATE(),0,''+@main_id+'','T_Disclose_DiscloseList',null,''+@jiaojie_id+'',''+@jiaojie_name+'',''+@sex+'',''+@cardid+'',''+@addr+'',''+@pic+'','否')
                    set @i+=1;
                     COMMIT TRAN ;    --要与 begintran 对应 
                    RETURN 1;
                       end
                    else
                        begin
                         ROLLBACK TRAN ;  --要与 begintran 对应 
                         RETURN 0 ;
                        end
                end    
                   
        insert into T_Disclose_DiscloseList 
        (_AutoID,_UserName,_OrgCode,_CreateTime,_UpdateTime,_IsDel,DiscloseId,DiscloseTitle,DisclosePerson,DisclosePersonList,DiscloseDatetime)
         values
         (''+@main_id+'',''+@username+'',''+@orgcode+'',GETDATE(),GETDATE(),0,''+@title_id+'',''+(select  Ttile from T_Disclose_DiscloseInfo where _AutoID=''+@title_id+'')+'',''+@jiaodi+'',''+@jiedi+'',GETDATE())
            COMMIT TRAN ;    --要与 begintran 对应 
            RETURN 1 ;         

 end

这里  错误和上面一样了  

后来想了下 正确是在循环里面  执行多次  

SqlServer中嵌套事务使用--事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配 －－根本问题

问题解决：链接http://blog.csdn.net/tuzhen007/article/details/11183961

或者看下一页