8WzmDM0H

1CREATE TABLE dbo.UserProfile
2(
3    [UserID] INT IDENTITY(1,1) NOT NULL,
4    [FirstName] NVARCHAR(30) NOT NULL,
5    [LastName] NVARCHAR(30) NOT NULL
6
7    CONSTRAINT [PK_UserProfile_UserID] 
8        PRIMARY KEY CLUSTERED ([UserID] ASC)
9);
10GO
11
12CREATE TABLE dbo.UserGroup
13(
14    [UserGroupID] INT IDENTITY(1,1) NOT NULL,
15    [UserID] INT NOT NULL, 
16    [GroupID] INT NOT NULL,
17    [IsActive] BIT NOT NULL DEFAULT((1)),
18
19    CONSTRAINT [PK_UserGroup_UserGroupID] 
20        PRIMARY KEY CLUSTERED ([UserGroupID] ASC),
21    CONSTRAINT [FK_UserGroup_GroupID_Group_GroupID] 
22        FOREIGN KEY ([GroupID]) REFERENCES [dbo].[Group] ([GroupID]),
23    CONSTRAINT [FK_UserGroup_UserID_UserProfile_UserID] 
24        FOREIGN KEY ([UserID]) REFERENCES [dbo].[UserProfile] ([UserId])
25);
26GO
27
28CREATE NONCLUSTERED INDEX [IX_GroupID] ON dbo.UserGroup(GroupID ASC); 
29CREATE NONCLUSTERED INDEX [IX_UserID] ON dbo.UserGroup(UserID ASC);  
30
31CREATE TABLE [dbo].[Group] 
32(
33    [GroupID] INT IDENTITY (1, 1) NOT NULL,
34    [PropertyID] INT NOT NULL,
35    [GroupName] NVARCHAR (100) NOT NULL,
36
37    CONSTRAINT [PK_Group_GroupID ]
38        PRIMARY KEY CLUSTERED ([GroupID] ASC),
39    CONSTRAINT [FK_Group_PropertyID_Property_PropertyID] 
40        FOREIGN KEY ([PropertyID]) REFERENCES [dbo].[Property] ([PropertyID]) 
41);
42GO
43
44CREATE NONCLUSTERED INDEX [IX_Group_PropertyID]
45ON [dbo].[Group]([PropertyID] ASC);
46GO
47	
48CREATE PROCEDURE [dbo].[InsertOrUpdateUG]
49    @UserID         INT,
50    @GroupID        INT,
51    @IsActive BIT,
52    @PropertyID     INT,
53    @ErrorTypeID INTEGER OUTPUT
54AS 
55BEGIN 
56    SET NOCOUNT, XACT_ABORT ON
57
58    SELECT @ErrorTypeID = 0
59
60    DECLARE @SQLError INTEGER = 0
61    DECLARE @RowCount INTEGER = 0
62    DECLARE @ReturnCode INTEGER = 0 
63    DECLARE @UserGroupID INT=0  
64    DECLARE @UTCNow DATETIME = GETUTCDATE()
65    DECLARE @StoredProcedureID INTEGER = 9060
66
67BEGIN
68    IF EXISTS (SELECT UserGroup.UserGroupID 
69               FROM UserGroup 
70               INNER JOIN [Group] ON UserGroup.GroupID = [Group].GroupID 
71               WHERE [Group].PropertyID = @PropertyID 
72                 AND UserGroup.UserID = @UserID)
73    BEGIN
74        SET @UserGroupID = (SELECT UserGroup.UserGroupID 
75                            FROM UserGroup 
76                            INNER JOIN [Group] ON UserGroup.GroupID = [Group].GroupID 
77                            WHERE [Group].PropertyID = @PropertyID 
78                              AND UserGroup.UserID = @UserID)
79        IF (@GroupID = 0)
80            UPDATE [UserGroup]
81            SET IsActive = @IsActive
82            WHERE UserGroupID = @UserGroupID
83        ELSE
84            UPDATE [UserGroup]
85            SET GroupID = @GroupID,
86                IsActive = @IsActive
87            WHERE UserGroupID = @UserGroupID
88        END
89    ELSE
90    BEGIN
91        IF (@GroupID != 0)
92        BEGIN
93            INSERT [UserGroup] (UserID, GroupID, IsActive)
94            VALUES (@UserID, @GroupID, @IsActive)
95        END
96    END
97END
98
99SELECT @SQLError = @@ERROR,
100       @RowCount = @@ROWCOUNT
101
102IF (@ErrorTypeID = 0) AND (@UserGroupID IS NULL) 
103BEGIN
104    SELECT @ErrorTypeID = @StoredProcedureID + 2 -- Player Not Found
105END
106END