普通游标/动态游标
--普通游标 ------------------------------------------------------ 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