· 8 years ago · Jun 04, 2018, 12:28 PM
1--======================================================
2-- Create Natively Compiled Stored Procedure Template
3--======================================================
4
5USE VkSpoiler_db
6GO
7
8-- Drop stored procedure if it already exists
9IF OBJECT_ID('dbo.ProcessNewUsers_Step1','P') IS NOT NULL
10 DROP PROCEDURE dbo.ProcessNewUsers_Step1
11GO
12
13CREATE PROCEDURE dbo.ProcessNewUsers_Step1
14 @GroupId int,
15 @List dbo.RowedIntArray READONLY
16WITH NATIVE_COMPILATION, SCHEMABINDING
17AS
18BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
19 DECLARE @Users AS dbo.RowedIntArray
20 DECLARE @Changes AS dbo.Helper_NewUsers1
21
22 INSERT INTO @Users (Value)
23 SELECT VkUserId
24 FROM dbo.GroupMembershipLinks
25 WHERE GroupId = @GroupId;
26
27 DECLARE @exists BIT = 0;
28 DECLARE @i INT = 1, @id INT;
29
30 -- Main iterator.
31 WHILE (@i > 0)
32 BEGIN
33 SET @id = 0;
34
35 SELECT TOP 1 @id = S.Value
36 FROM @List S
37 WHERE S.RowId = @i;
38
39 IF (@id = 0)
40 SET @i = 0;
41 ELSE
42 BEGIN
43 SET @exists = 0;
44
45 SELECT TOP 1 @exists = 1
46 FROM @Users
47 WHERE Value = @id;
48
49 IF (@exists = 0)
50 BEGIN
51 INSERT INTO dbo.GroupMembershipLinks (VkUserId, GroupId)
52 VALUES (@id, @GroupId);
53
54 INSERT INTO @changes (id, joined)
55 VALUES (@id, 1);
56 END
57 ELSE
58 BEGIN
59 DELETE FROM @Users
60 WHERE Value = @id;
61 END
62
63 SET @i += 1;
64 END
65 END
66
67 -- Inserting those who have left the group.
68 INSERT INTO @Changes (id, joined)
69 SELECT Value, 0
70 FROM @Users;
71
72 -- Prepeare yet another temp table.
73 DECLARE @Leavers AS dbo.RowedIntArray
74
75 INSERT INTO @Leavers (Value)
76 SELECT Value
77 FROM @Users;
78
79 -- Delete from GroupMembershipLinks those who have left the group.
80 SET @i = 1;
81 WHILE (@i > 0)
82 BEGIN
83 SET @id = 0;
84
85 SELECT TOP 1 @id = S.Value
86 FROM @Leavers S
87 WHERE S.RowId = @i;
88
89 IF (@id = 0)
90 SET @i = 0;
91 ELSE
92 BEGIN
93 DELETE FROM dbo.GroupMembershipLinks
94 WHERE VkUserId = @id;
95
96 SET @i += 1;
97 END
98 END
99
100 -- Return the result.
101 SELECT id, joined
102 FROM @Changes;
103END