--建议做如下修改:
/*用户的消费*/
/*代码:王中涛*/
CREATE PROCEDURE Sp_User_Consume
@lngUserId Bigint, /*消费用户id*/
@intCount Int, /*消费数量,以点数计算*/
@strCause Nvarchar(100), /*消费的事项*/
@intBackValue TinyInt Output /*返回值,0:成功;1:消费卡余额不足; -1:处理出错*/
AS
Set NoCount On Declare @intCardBalance Int
Declare @intTotalCount Int
Declare @intCardType Tinyint Declare @intError Int
Select @intCardBalance=CardBalance,@intCardType=CardType
From Tbl_User_Info Where UserId=@lngUserId /*根据消费卡类型打折*/
Select @intCount=
Case @intCardType
When 1 Then @intCount
When 2 Then @intCount*0.9
When 3 Then @intCount*0.8
When 4 Then @intCount*0.7
End If @intCardBalance<@intCount /*消费卡余额 不足*/
set @intBackValue=1
Else
Begin
Begin Transaction /*把这次消费记录到消费纪录中*/
Insert Into Tbl_User_ConsumeRecord(UserId,ConsumeCount,ConsumeReason)
Values(@lngUserId,@intCount,@strCause)
Set @intError=@@Error
if @intError<>0 goto lb_process --这里要立即判断是否出错
/*计算出该用户在最近90天的消费总和*/
/*Select @intTotalCount=Sum(ConsumeCount) From Tbl_User_ConsumeRecord Where (Datediff(dy, ConsumeDate, Getdate()) < 90) Group By UserId Having (UserId = @lngUserId)*/
/*消费卡升级*/
/*在该用户的消费卡中减去消费的数量并且对消费卡升级*/
Update Tbl_User_Info Set
CardBalance=CardBalance-@intCount,
CardType=@intCardType
Where UserId=@lngUserId
Select @intError=@intError+@@Errorlb_process:
If @intError<>0
begin
ROLLBACK TRAN --如果处理出错,还是返回0,成功吗?
set @intBackValue=-1
end
Else
begin
Commit Transaction
Set @intBackValue=0
end
End
GO
/*用户的消费*/
/*代码:王中涛*/
CREATE PROCEDURE Sp_User_Consume
@lngUserId Bigint, /*消费用户id*/
@intCount Int, /*消费数量,以点数计算*/
@strCause Nvarchar(100), /*消费的事项*/
@intBackValue TinyInt Output /*返回值,0:成功;1:消费卡余额不足; -1:处理出错*/
AS
Set NoCount On Declare @intCardBalance Int
Declare @intTotalCount Int
Declare @intCardType Tinyint Declare @intError Int
Select @intCardBalance=CardBalance,@intCardType=CardType
From Tbl_User_Info Where UserId=@lngUserId /*根据消费卡类型打折*/
Select @intCount=
Case @intCardType
When 1 Then @intCount
When 2 Then @intCount*0.9
When 3 Then @intCount*0.8
When 4 Then @intCount*0.7
End If @intCardBalance<@intCount /*消费卡余额 不足*/
set @intBackValue=1
Else
Begin
Begin Transaction /*把这次消费记录到消费纪录中*/
Insert Into Tbl_User_ConsumeRecord(UserId,ConsumeCount,ConsumeReason)
Values(@lngUserId,@intCount,@strCause)
Set @intError=@@Error
if @intError<>0 goto lb_process --这里要立即判断是否出错
/*计算出该用户在最近90天的消费总和*/
/*Select @intTotalCount=Sum(ConsumeCount) From Tbl_User_ConsumeRecord Where (Datediff(dy, ConsumeDate, Getdate()) < 90) Group By UserId Having (UserId = @lngUserId)*/
/*消费卡升级*/
/*在该用户的消费卡中减去消费的数量并且对消费卡升级*/
Update Tbl_User_Info Set
CardBalance=CardBalance-@intCount,
CardType=@intCardType
Where UserId=@lngUserId
Select @intError=@intError+@@Errorlb_process:
If @intError<>0
begin
ROLLBACK TRAN --如果处理出错,还是返回0,成功吗?
set @intBackValue=-1
end
Else
begin
Commit Transaction
Set @intBackValue=0
end
End
GO
*Select @intTotalCount=Sum(ConsumeCount) From Tbl_User_ConsumeRecord Where (Datediff(dy, ConsumeDate, Getdate()) < 90) Group By UserId Having (UserId = @lngUserId)*/
上面这句,如果用的话,最好改为:Select @intTotalCount=Sum(ConsumeCount) From Tbl_User_ConsumeRecord Where ConsumeDate<=Getdate()- 90 and UserId = @lngUserId
Group By UserId