CREATE PROC BOK_LevelUp(@Newid int)
as
declare @c11 int
declare @c10 int
declare @Visitant_Count int
declare @Level int
declare @c01 int
declare @Vchievement int
declare @c31 nvarchar(500)
declare @c16 int
declare @money money
declare @Old_Level int begin
set @Visitant_Count = 0
set @Level= 0 select @c11 = c11 from c where c01=@Newid
select @Old_Level = level from c where c01=@c11
select @Visitant_Count = count(1)
from c
where c11=@c11
and c10 = 1 and c17 in(2,3)
if(@Visitant_Count >=3)
begin
set @Level = 1
end
if(@Level<>@Old_Level)
begin
update c set level=@Level where c01 = @c11
end declare myCursor cursor for
select c01 from c t1 where c17 in(2,3)
and (select count(1) from c t2
where t2.c10=1 and t2.c11 = t1.c01 and c17 in(2,3) )>=5
open myCursor
fetch myCursor into @c01
set @money= 33*10000
while(@@fetch_status = 0)
begin
set @Vchievement =0
select @c31=c31,
@c16=c16
from c where c01=@c01
select @Vchievement = isnull(sum(cr02),0)
from c left join cr on cr.c02 = c.c02
where charindex(@c31+'-',c31)=1 and (c16-@c16)<=10 and c17 in(2,3)
if(@Vchievement>=@money)
begin
update c set level=2 where c01=@c01
end
fetch myCursor into @c01
end
close myCursor
deallocate myCursor declare myCursor cursor for
select c01 from c t1 where c17 in(2,3)
and (select count(1) from c t2
where t2.c10=1 and t2.c11 = t1.c01 and c17 in(2,3) )>=5
open myCursor
fetch myCursor into @c01
set @money= 66*10000
while(@@fetch_status = 0)
begin
set @Vchievement =0
select @c31=c31,
@c16=c16
from c where c01=@c01
select @Vchievement = isnull(sum(cr02),0)
from c left join cr on cr.c02 = c.c02
where charindex(@c31+'-',c31)=1 and (c16-@c16)<=10 and c17 in(2,3)
if(@Vchievement>=@money)
begin
update c set level=3 where c01=@c01
end
fetch myCursor into @c01
end
close myCursor
deallocate myCursor
return
end
as
declare @c11 int
declare @c10 int
declare @Visitant_Count int
declare @Level int
declare @c01 int
declare @Vchievement int
declare @c31 nvarchar(500)
declare @c16 int
declare @money money
declare @Old_Level int begin
set @Visitant_Count = 0
set @Level= 0 select @c11 = c11 from c where c01=@Newid
select @Old_Level = level from c where c01=@c11
select @Visitant_Count = count(1)
from c
where c11=@c11
and c10 = 1 and c17 in(2,3)
if(@Visitant_Count >=3)
begin
set @Level = 1
end
if(@Level<>@Old_Level)
begin
update c set level=@Level where c01 = @c11
end declare myCursor cursor for
select c01 from c t1 where c17 in(2,3)
and (select count(1) from c t2
where t2.c10=1 and t2.c11 = t1.c01 and c17 in(2,3) )>=5
open myCursor
fetch myCursor into @c01
set @money= 33*10000
while(@@fetch_status = 0)
begin
set @Vchievement =0
select @c31=c31,
@c16=c16
from c where c01=@c01
select @Vchievement = isnull(sum(cr02),0)
from c left join cr on cr.c02 = c.c02
where charindex(@c31+'-',c31)=1 and (c16-@c16)<=10 and c17 in(2,3)
if(@Vchievement>=@money)
begin
update c set level=2 where c01=@c01
end
fetch myCursor into @c01
end
close myCursor
deallocate myCursor declare myCursor cursor for
select c01 from c t1 where c17 in(2,3)
and (select count(1) from c t2
where t2.c10=1 and t2.c11 = t1.c01 and c17 in(2,3) )>=5
open myCursor
fetch myCursor into @c01
set @money= 66*10000
while(@@fetch_status = 0)
begin
set @Vchievement =0
select @c31=c31,
@c16=c16
from c where c01=@c01
select @Vchievement = isnull(sum(cr02),0)
from c left join cr on cr.c02 = c.c02
where charindex(@c31+'-',c31)=1 and (c16-@c16)<=10 and c17 in(2,3)
if(@Vchievement>=@money)
begin
update c set level=3 where c01=@c01
end
fetch myCursor into @c01
end
close myCursor
deallocate myCursor
return
end
如果存储过程中包含的一些语句并不返回许多实际数据,则该设置由于大量减少了网络流量,因此可显著提高性能。