create table guest(id int,num int,status int)
go
declare @id int ,@rowcount int
select top 1 @id=id from guest where status=0
set @rowcount=@@rowcount
while @rowcount=1
begin
if num>=5 and num<30
exec dbo.sp @id,110,1
if num>=30 and num<70
exec dbo.sp @id,110,6
if num>=70 and num<100
exec dbo.sp @id,110,12
if num>=100 and num<200
exec dbo.sp @id,110,24
if num>=200
exec dbo.sp @id,110,48
update gguest set status=1 where id=@id
goto next_chr
----------------------------------
next_chr:
select top 1 @id=id from guest where status=0
set @rowcount=@@rowcount
end
go想要实现的就是guest表中的num值在那几个区间的时候调用不同的存储过程。这个条件语句我不太会用,条件语句不是if..elseif...else...endif吗??
go
declare @id int ,@rowcount int
select top 1 @id=id from guest where status=0
set @rowcount=@@rowcount
while @rowcount=1
begin
if num>=5 and num<30
exec dbo.sp @id,110,1
if num>=30 and num<70
exec dbo.sp @id,110,6
if num>=70 and num<100
exec dbo.sp @id,110,12
if num>=100 and num<200
exec dbo.sp @id,110,24
if num>=200
exec dbo.sp @id,110,48
update gguest set status=1 where id=@id
goto next_chr
----------------------------------
next_chr:
select top 1 @id=id from guest where status=0
set @rowcount=@@rowcount
end
go想要实现的就是guest表中的num值在那几个区间的时候调用不同的存储过程。这个条件语句我不太会用,条件语句不是if..elseif...else...endif吗??
declare cur cursor select id ,numfrom gruest where status = 0
open cur
fetch cur into @id,@num
while (@@fetch_status = 0)
begin
if (@num>=5 and @num <30 )
exec dbo.sp @id,110,1
if (@num>=30 and @num <70 )
exec dbo.sp @id,110,6
if (@num>=70 and @num <100)
exec dbo.sp @id,110,12
if (@num>=100 and @num <200)
exec dbo.sp @id,110,24
if (@num>=200
exec dbo.sp @id,110,48
fetch cur into @id,@num
end
close cur
deallocate cur
将表里的NUM id 复制给变量
然后用变量判断你 直接用NUM ID 是不对哦的