SQL 游标foreach 表

declare @auid int,@aulname varchar(20),@aufname varchar(20), @st char(2),@auinfo varchar(50)

declare auth_cur cursor for

select tp_id, nvarchar19

from AllUserData where
tp_ListId='85CD0CD4-D471-49D6-9765-FB3D27F136E2'

open auth_cur

fetch next from auth_cur into @auid,@aulname

while (@@fetch_status=0)

begin

--print cast(@aulname as decimal(10,4))

select cast(@aulname as decimal(10,4))

fetch next from auth_cur into @auid,@aulname

end

close auth_cur

deallocate auth_cur

 

 

 

------

fetch next from somain into @poid,@cpoid,@cexch_name
while (@@fetch_status<>-1)
begin
--set @autoid=(select isnull(max(autoid),0) from so_somain)+1
--set @autoid=isnull((select ifatherid from ufsystem.dbo.ua_identity where cacc_id='010' and cvouchtype='somain'),0)
--select @poid,@cpoid,@autoid
--set @autoid=(select count(*) from so_somain)+1
set @autoid=(select max(id) from so_somain)+1--08.18 byzzq
Insert Into SO_SOMain(cDefine14,cDefine15,id,cstcode,ddate,csocode,ccuscode,cdepcode,cexch_name,itaxrate,istatus,cmaker,cbustype,iverifystate)
values(@poid,cast(@poid as int),@autoid,'01',getdate(),@cpoid,'SZHL','HS1501','人民币',17,0,'zzq','委托代销',0)
update ufsystem.dbo.ua_identity set ifatherid=@autoid where cacc_id='115' and cvouchtype='somain'
fetch next from somain into @poid,@cpoid,@cexch_name
end
close somain
deallocate somain