CREATE procedure [dbo].[sp_TableSave]

@TypeID tinyint,    --     0 新增，1 修改，2 删除

@ID int,

@Name nvarchar(32),

@ProductIDs varchar(1024),

@BackColor nvarchar(128),

@UID int,

@Creator nvarchar(32),

@Error nvarchar(100)='' output

as

set nocount on

begin try

begin transaction    --    开始事务

 declare @currDate SMALLDATETIME

 SET @currDate=GETDATE()

 if(@TypeID='')  --修改

 begin

   update ProductActivity set Name=@Name,ProductIDs=@ProductIDs,Backcolor=@BackColor,IsPush=1 where ActivityID=@ID        

   -- 更新移除产品log

   insert into OperateLog(UID,UserName,Title,Content,CreateTime)

   select @UID,@Creator,'更新',CAST(ProductID as varchar(10))+' /Activity:0',@currDate

   from Product AS p

   WHERE ActivityID=@ID and NOT EXISTS(

  select 1 from dbo.F_split(@ProductIDs,',') AS t where t.f=p.ProductID

   )      

   -- 更新移除产品

   update p set ActivityID=0,IsPush=(case when p.status=0 then 1 else 0 end)

   from Product AS p

   WHERE ActivityID=@ID and NOT EXISTS(

  select 1 from dbo.F_split(@ProductIDs,',') AS t where t.f=p.ProductID

   )       

   -- 更新新增产品log

   insert into OperateLog(UID,UserName,Title,Content,CreateTime)

   select @UID,@Creator,'更新',CAST(ProductID as varchar(10))+' /Activity:'+CAST(@ID as varchar(10)),@currDate

   from Product AS p

   WHERE ActivityID<>@ID and EXISTS(

  select 1 from dbo.F_split(@ProductIDs,',') AS t where t.f=p.ProductID

   )      

   -- 更新新增产品

   update p set ActivityID=@ID,IsPush=(case when p.status=0 then 1 else 0 end)

   from Product AS p

   WHERE ActivityID<>@ID and EXISTS(

  select 1 from dbo.F_split(@ProductIDs,',') AS t where t.f=p.ProductID

   )

 end

 else if(@TypeID='') --删除

 begin

   -- 删除 ProductActivity

   update ProductActivity set Status=1,IsPush=1 where ActivityID=@ID        

   -- 写入product改动log

   insert into OperateLog(UID,UserName,Title,Content,CreateTime)

   select @UID,@Creator,'更新',cast(ProductID as varchar(10))+'/Activity:0',@currDate from Product where ActivityID=@ID        

   -- 更新product

   update Product set ActivityID=0,IsPush=(case when Product.status=0 then 1 else 0 end) where ActivityID=@ID

 end

 else      --新增

 begin

   -- 更新ProductActivity表

   declare @NewID int

   insert into ProductActivity(Name,ProductIDs,Backcolor,Creator,CreateTime,IsPush)

   values(@Name,@ProductIDs,@BackColor,@Creator,@currDate,1)

   set @NewID = SCOPE_IDENTITY()        

   -- 更新product表

   update p set ActivityID=@NewID, IsPush=(case when p.Status=0 then 1 else 0 end)

   FROM Product AS p

   INNER JOIN dbo.F_split(@ProductIDs,',') AS t ON t.f=p.ProductID      

   -- 写product改动log

   insert into OperateLog(UID,UserName,Title,Content,CreateTime)

   select @UID,@Creator,'更新', CAST(ProductID as varchar(10))+'/Activity:'+CAST(@NewID as varchar(10)),@currDate

   from Product where ActivityID=@NewID

 end

 set @Error=''

commit transaction  --    提交事务

return;

end try

begin catch     --    异常

  set @Error='1-'+ERROR_MESSAGE()

  rollback transaction  --    回滚事务

  return;

end catch