积分等级的存储过程

exec setScores 'test','test'

alter proc setScores (@userName varchar(120),@password varchar(120))
as
begin
declare @count int
declare @lastDate datetime
declare @gid varchar(60)
declare @sum int
declare @level varchar(60)
---年度变量
declare @regDate datetime
declare @yearCount int
---函数主题
select @gid=Gid from CusUsers where [User_Name]=@userName
select @count=count(1) from CusUsers where [User_Name]=@userName and User_Pass=@password
if(@count>0)
begin
select @lastDate=gdate from UserScrores where uid=@gid and stype=1 order by gdate desc
set @lastDate=isnull(@lastDate,getdate()-1)
if(datediff(day,@lastDate,getdate())>=1)
begin
--添加登陆日志
insert into UserScrores(stype,uid,Scores) select '1',@gid,10

end
--年度分设置
select @regDate=Gdate from CusUsers where  Gid=@gid
set @yearCount=datediff(year,@regDate,getdate())
if(@yearCount>=0)
begin
print '10'
update UserScrores set Scores=50 where uid=@gid and stype=10
if(@@rowcount<=0)
insert into UserScrores(stype,uid,Scores) select '10',@gid,50
end
else if (@yearCount>=1 and @yearCount<=2)
begin
update UserScrores set Scores=50 where uid=@gid and stype=11
if(@@rowcount<=0)
insert into UserScrores(stype,uid,Scores) select '11',@gid,50
end
else if(@yearCount>=3)
begin
update UserScrores set Scores=50 where uid=@gid and stype=12
if(@@rowcount<=0)
insert into UserScrores(stype,uid,Scores) select '12',@gid,50
end

---设置级别
select @sum=sum(isnull(Scores,0)) from UserScrores where uid=@gid
select @level=levelMark from LevelTable where @sum between SStart and SEnd
update CusUsers set ULevel=@level where Gid=@gid
--设置结束
end

end

GO

select * from LevelTable