mssql 游标的使用

set     ANSI_NULLS   on  
set     ANSI_WARNINGS   on     go
CREATE PROCEDURE test AS 
declare @sql varchar(1000)
declare @nsql varchar(200)
declare @i int
set @i=0

set @sql='select neto.id,neto.fdmoney as netfdmoney,loco.fdmoney as locfdmoney into temptable  from OPENDATASOURCE (''SQLOLEDB'',
      ''data source=服务器名或IP;user id=用户名;password=密码'').hcibe.dbo.order_form as neto,bak.dbo.order_f as loco '
set @sql=@sql+' where neto.id=loco.orderid  and loco.dp_time>''2006-12-5'' and neto.fdmoney<>loco.fdmoney and loco.clok in (60)'

--将数据存入临时表
exec(@sql)

DECLARE tnames_cursor CURSOR
FOR
   SELECT id,netfdmoney
   FROM temptable
OPEN tnames_cursor

--定义存储变量
declare @orderid varchar(20),@netfdmoney money

FETCH next from  tnames_cursor into  @orderid,@netfdmoney
while @@fetch_status=0
begin
 set @nsql='update order_f set fdmoney='+convert(varchar(50),@netfdmoney)+' where orderid='+@orderid
 exec(@nsql)
FETCH next from  tnames_cursor into  @orderid,@netfdmoney
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

--删除临时表
--drop table temptable
GO