sql游标和动态游标

--普通游标

------------------------------------------------------
declare @sql nvarchar(500) --sql必须是nvarchar类型
declare @task_id bigint
declare @task_ids varchar(30)
declare @orgcode varchar(2)
declare @count int

declare @tableName varchar(100)--这个变量是个varchar

declare @datatableName varchar(100)
declare orgcode_cursor cursor
for
 select orgcode  from lib_org where len(orgcode)=2 and orgcode <> -1
open orgcode_cursor 
fetch next from orgcode_cursor into @orgcode
while(@@fetch_status =0)
begin
 ---开始取一个省的所有任务id

 set @tableName = 'lib_task_p'+@orgcode

 --set @datatableName = 'lib_task3_p'+@orgcode
 --set @task_ids = '3,6,9'
 --set @datatableName = 'lib_task2_p'+@orgcode
-- set @task_ids = '2,5,8'
set @datatableName = 'lib_task1_p'+@orgcode
set @task_ids = '1,4,7'



set @sql = 'select  @a=count(*) from '+@tableName+'  where uiid not in(select fk_task_id from '+@datatableName+') and yyyy = 2009 and mm in(7,8) and fk_task_level = 3 and fk_tasktype_id in('+@task_ids+') and actstatus<>0'
EXEC sp_executesql @sql,N'@a decimal(18,0) output',@count output	

if @count<>0
begin
	print 'orgcode:'
	
	print @orgcode
	print @count
end

 ---结束取一个省的所有任务id
 fetch next from orgcode_cursor into @orgcode
end
close orgcode_cursor
deallocate orgcode_cursor



--动态游标

declare @sql nvarchar(500)
declare @tablename varchar(20)
declare @task_id bigint
set @tablename = 'lib_org'
--只有下面两句有区别
set @sql =  'declare pcursor cursor for select uiid from '+@tablename
exec (@sql)

open pcursor
fetch next from pcursor into @task_id
while(@@fetch_status=0)
begin
print @task_id
fetch next from pcursor into @task_id
end
close pcursor
deallocate pcursor

---------------------------------------

评论

1 楼 vanillalyx 2009-09-29  

http://www.zxbc.cn/html/20080112/31306.html3种实现SQLSERVER动态游标的方法