游标的使用

 1 declare @Id uniqueidentifier
 2 declare @UserId uniqueidentifier
 3 declare @DepartmentName nvarchar(128)
 4 declare @DepartmentId uniqueidentifier
 5 declare @departmentCount int
 6 declare cursor1 cursor for select Id from PersonSummary        --定义游标cursor1
 7 
 8 
 9 open cursor1    
10 
11 fetch next from cursor1 into @Id
12 
13 
14 while @@fetch_status=0           --判断是否成功获取数据
15 begin
16 
17 select @UserId=userId from PersonSummary where id=@Id
18 select @departmentCount=COUNT(*) from PersonSummaryDepartment where PersonSummaryId=@Id
19 if (@departmentCount <1)
20     begin
21         insert into PersonSummaryDepartment(PersonSummaryId,DepartmentId,DepartmentName)
22         select @id,a.DepartmentId,b.Name from [RSGPMS] .dbo.DepartmentUserRef a,[RSGPMS] .dbo.Department b where  a.UserId=@UserId and a.DepartmentId = b.Id
23 
24     end
25 fetch next from cursor1 into @Id  --将游标向下移1行
26 end
27 
28 close cursor1                   --关闭游标
29 deallocate cursor1
View Code