添加表，然后根据是否添加关系isRelation来添加关系表（放入事务中）
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE 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)ASBegin 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 ONset QUOTED_IDENTIFIER ONgoCREATE 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)ASBegin 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