sql server 存储过程、事务，增删改

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)='1' output        
as        
set nocount on         
begin try        
begin transaction    --    开始事务
 declare @currDate SMALLDATETIME      
 SET @currDate=GETDATE()     
 if(@TypeID='1')  --修改        
 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='2') --删除        
 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='0'        
commit transaction  --    提交事务
return;        
end try        
begin catch     --    异常
  set @Error='1-'+ERROR_MESSAGE()        
  rollback transaction  --    回滚事务
  return;        
end catch

 

转载于:https://www.cnblogs.com/flywing/p/5006509.html