在存储过程中用事务

添加表，然后根据是否添加关系isRelation来添加关系表（放入事务中）

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[AddModule]
(
    @ModuleName nvarchar(50),
    @ParentID int,
    @LanguageID int,
    @OrderNo int,
    @BannerUrl varchar(150),
    @IconUrl varchar(150),
    @Url varchar(150),
    @ModuleManageUrl varchar(150),
    @InfoManageUrl varchar(150),
    @IsVisible bit,
    @IsEnabled bit,
    @Remark nvarchar(200),
    @moduleRelationId int,
    @isRelation bit
)
AS
Begin
    declare @sql varchar(max)
    declare @TempModuleID int
    begin   tran  
    begin try
            insert into Module (ModuleName,ParentID,LanguageID,OrderNo,BannerUrl,IconUrl,Url,ModuleManageUrl,InfoManageUrl,IsVisible,IsEnabled,Remark)
            values(@ModuleName,@ParentID,@LanguageID,@OrderNo,@BannerUrl,@IconUrl,@Url,@ModuleManageUrl,@InfoManageUrl,@IsVisible,@IsEnabled,@Remark);
            set @TempModuleID=@@identity
            if @isRelation=1
            insert into ModuleRelation(ModuleIDs)values(Cast(@TempModuleID as varchar)+','+Cast(@moduleRelationId as varchar))            
            commit tran
            set @sql='select '+Cast(@TempModuleID as varchar)+' as ModuleID'
    end try
    begin catch            
            rollback tran
            set @sql='select 0 as ModuleID'            
    end catch
    execute (@sql)    
END

更新表和关系表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







CREATE PROCEDURE [dbo].[UpdateModule]
(
    @ModuleName nvarchar(50),
    @ParentID int,
    @LanguageID int,
    @OrderNo int,
    @BannerUrl varchar(150),
    @IconUrl varchar(150),
    @Url varchar(150),
    @ModuleManageUrl varchar(150),
    @InfoManageUrl varchar(150),
    @IsVisible bit,
    @IsEnabled bit,
    @Remark nvarchar(200),
    @ModuleID int,
    @moduleRelationId int,
    @isRelation bit
)
AS
Begin
    declare @sql varchar(max)
    declare @TempModuleRelationID int
    begin   tran  
    begin try
            --更新模块信息
            update Module set ModuleName=@ModuleName,ParentID=@ParentID,LanguageID=@LanguageID,OrderNo=@OrderNo,
                BannerUrl=@BannerUrl,IconUrl=@IconUrl,Url=@Url,ModuleManageUrl=@ModuleManageUrl,InfoManageUrl=@InfoManageUrl,IsVisible=@IsVisible,IsEnabled=@IsEnabled,Remark=@Remark
                where moduleId=@moduleId
            --是否修改关系
            if @isRelation=1
            begin
                --判断该模块是否已存在关系
                select @TempModuleRelationID=ModuleRelationID from ModuleRelation where charindex(','+Cast(@ModuleID as varchar)+',',','+ModuleIDs+',')!=0            
                if(@TempModuleRelationID is null)                
                        insert into ModuleRelation(ModuleIDs)values(Cast(@ModuleID as varchar)+','+Cast(@moduleRelationId as varchar))
                else
                    update ModuleRelation set ModuleIDs=(Cast(@ModuleID as varchar)+','+Cast(@moduleRelationId as varchar)) where ModuleRelationID=@TempModuleRelationID            
            end
            commit tran
            set @sql='select 1 as result'
    end try
    begin catch            
            rollback tran
            set @sql='select 0 as result'        
    end catch
    execute (@sql)
END