set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
close tablename
deallocate tablename--关闭游标
drop PROCEDURE [dbo].[updatecard_point]--删除存储过程
go
create PROCEDURE [dbo].[updatecard_point]--创建存储过程
AS
BEGIN
SET NOCOUNT ON;
DECLARE tablename CURSOR FOR SELECT cardid FROM bbb --定义一个游标保存数据
OPEN tablename
DECLARE @tablename VARCHAR(50), @point int
FETCH NEXT FROM tablename INTO @tablename --从游标读取数据赋值给变量
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Rcount int, @Rcount1 int,@sum int
select @point = 60+floor(rand()*(80-60+1)) --定义一个变量60~80
update wcm_product_buy set exam_point=@point,if_pass_exam =1 where user_id in(select id from wcm_regUser_info where name=@tablename and web_id=3) and product_id=821 --更新数据
set @Rcount=@@ROWCOUNT
select @point = 60+floor(rand()*(80-60+1))
update wcm_product_buy1 set exam_point=@point,if_pass_exam =1 where user_id in(select id from wcm_regUser_info where name=@tablename and web_id=3) and product_id=821
set @Rcount1=@@ROWCOUNT
set @sum=@Rcount+@Rcount1
if @sum =0
begin
update bbb set statues=0 where cardid=@tablename;
end
else if @sum =1
begin
update bbb set statues=1 where cardid=@tablename;
end
else if @sum =2
begin
update bbb set statues=2 where cardid=@tablename;
end
FETCH NEXT FROM tablename INTO @tablename; --循环取出数据
END
CLOSE tablename
DEALLOCATE tablename
END
exec [dbo].[updatecard_point]
这里要注意的是@@ROWCOUNT 他只返回最近一句update的结果，多条update建议定义一个变量存起来
存储过程和游标（简洁，只需数据库操作就可实现大量数据更新功能）
原文：http://www.cnblogs.com/ly133333333333333/p/5209300.html