USE [tanjiaping]GO/****** Object: StoredProcedure [dbo].[pro_cutPoint] Script Date: 03/02/2011 17:54:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
ALTER procedure [dbo].[pro_cutPoint]--要扣除的总分数@sumPoint int,--表UserPointRecord用到的数据@UserID nvarchar(50),--用户ID--表User_UsedPoint用到的数据@RecordTypeId int,@RecordType nvarchar(1000),@OrderID int,--表User_PointUseHistory用到的数据@ActionID int,@ActionName varchar(50)as--开启自动回滚事务set xact_abort onbegin transaction cutTran
declare cursor_cutPoint scroll cursorforselect PointRecord_Id,Point,UserPoint from UserPointRecord where Point>UserPoint and UserID=@UserID and CreateDate>DATEADD(MONTH,-18,GETDATE()) and IsValid=1 order by CreateDate asc--某用户的所有未用完积分的记录
declare @PointRecord_Id int;declare @Point int;declare @UserPoint int;
open cursor_cutPointfetch next from cursor_cutPoint into @PointRecord_Id,@Point,@UserPointwhile (@@FETCH_STATUS=0)begin if(@sumPoint>(@Point-@UserPoint))--如果要扣除的分数大于当前记录剩余的分数 begin set @sumPoint=@sumPoint-(@Point-@UserPoint) update UserPointRecord set UserPoint=@Point where PointRecord_Id=@PointRecord_Id if(@@error<>0) begin rollback transaction cutTran end insert into User_UsedPoint values(@RecordTypeId,@RecordType,@PointRecord_Id,@OrderID,-(@Point-@UserPoint),0,GETDATE(),1) if(@@error<>0) begin rollback transaction cutTran end insert into User_PointUseHistory values(@ActionID,@ActionName,-(@Point-@UserPoint),@UserID,GETDATE(),@UserID,GETDATE()) if(@@error<>0) begin rollback transaction cutTran end end else if(@sumPoint<=(@Point-@UserPoint)) begin update UserPointRecord set UserPoint=@sumPoint+@UserPoint where PointRecord_Id=@PointRecord_Id if(@@error<>0) begin rollback transaction cutTran end insert into User_UsedPoint values(@RecordTypeId,@RecordType,@PointRecord_Id,@OrderID,-@sumPoint,0,GETDATE(),1) if(@@error<>0) begin rollback transaction cutTran end insert into User_PointUseHistory values(@ActionID,@ActionName,-@sumPoint,@UserID,GETDATE(),@UserID,GETDATE()) if(@@error<>0) begin rollback transaction cutTran end break end fetch next from cursor_cutPoint into @PointRecord_Id,@Point,@UserPointendclose cursor_cutPointdeallocate cursor_cutPointcommit transaction cutTran
