游标 循环插入值

 1  DECLARE @AccessRightFK VARCHAR(500)
 2  DECLARE @UserGroupFK uniqueidentifier
 3 
 4  DECLARE UserGroup_Cursor CURSOR FOR
 5  
 6  SELECT AccessRightFK,UserGroupFK
 7  FROM sec.UserGroup_AccessRight ugar WHERE ugar.AccessRightFK = 'Client/Module/QuotationTemplate'
 8 
 9 
10  
11  OPEN UserGroup_Cursor
12  
13  FETCH NEXT FROM UserGroup_Cursor 
14  INTO @AccessRightFK, @UserGroupFK
15  
16  WHILE @@FETCH_STATUS = 0
17  BEGIN     
18     DECLARE @AccessRight nvarchar(100)
19     DECLARE @RecordCount int = 0
20 
21      -- Find if exist
22     SELECT @RecordCount=COUNT(1)
23     FROM sec.UserGroup_AccessRight ugar
24     WHERE ugar.UserGroupFK=@UserGroupFK AND ugar.AccessRightFK = 'Client/Module/CaseFolder/QuotationView'
25 
26     -- Find AccessRight
27     SELECT @AccessRight = ugar.AccessRight
28     FROM sec.UserGroup_AccessRight ugar
29     WHERE ugar.UserGroupFK=@UserGroupFK AND ugar.AccessRightFK = @AccessRightFK
30 
31     -- Insert if not exist
32      IF @RecordCount = 0
33     BEGIN
34         INSERT INTO SEC.UserGroup_AccessRight
35         (
36             AccessRightFK,
37             UserGroupFK,
38             AccessRight
39         )
40         VALUES
41         (
42             'Client/Module/CaseFolder/QuotationView', -- AccessRightFK - varchar
43             @UserGroupFK, -- UserGroupFK - uniqueidentifier
44             @AccessRight -- AccessRight - varchar
45         )
46     END
47      
48       FETCH NEXT FROM UserGroup_Cursor 
49     INTO @AccessRightFK, @UserGroupFK
50  END 
51  
52  
53  CLOSE UserGroup_Cursor;
54  DEALLOCATE UserGroup_Cursor;
55 
56  

 

posted @ 2020-03-11 17:48  b小青青  阅读(289)  评论(1编辑  收藏  举报