You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
86 lines
2.5 KiB
86 lines
2.5 KiB
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_UpdateMany] |
|
@jsonData NVARCHAR(MAX) |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
DECLARE @UserIds [dbo].[GuidIdArray] |
|
|
|
-- Parse the JSON string |
|
DECLARE @OrganizationUserInput AS TABLE ( |
|
[Id] UNIQUEIDENTIFIER, |
|
[OrganizationId] UNIQUEIDENTIFIER, |
|
[UserId] UNIQUEIDENTIFIER, |
|
[Email] NVARCHAR(256), |
|
[Key] VARCHAR(MAX), |
|
[Status] SMALLINT, |
|
[Type] TINYINT, |
|
[ExternalId] NVARCHAR(300), |
|
[CreationDate] DATETIME2(7), |
|
[RevisionDate] DATETIME2(7), |
|
[Permissions] NVARCHAR(MAX), |
|
[ResetPasswordKey] VARCHAR(MAX), |
|
[AccessSecretsManager] BIT |
|
) |
|
|
|
INSERT INTO @OrganizationUserInput |
|
SELECT |
|
[Id], |
|
[OrganizationId], |
|
[UserId], |
|
[Email], |
|
[Key], |
|
[Status], |
|
[Type], |
|
[ExternalId], |
|
[CreationDate], |
|
[RevisionDate], |
|
[Permissions], |
|
[ResetPasswordKey], |
|
[AccessSecretsManager] |
|
FROM OPENJSON(@jsonData) |
|
WITH ( |
|
[Id] UNIQUEIDENTIFIER '$.Id', |
|
[OrganizationId] UNIQUEIDENTIFIER '$.OrganizationId', |
|
[UserId] UNIQUEIDENTIFIER '$.UserId', |
|
[Email] NVARCHAR(256) '$.Email', |
|
[Key] VARCHAR(MAX) '$.Key', |
|
[Status] SMALLINT '$.Status', |
|
[Type] TINYINT '$.Type', |
|
[ExternalId] NVARCHAR(300) '$.ExternalId', |
|
[CreationDate] DATETIME2(7) '$.CreationDate', |
|
[RevisionDate] DATETIME2(7) '$.RevisionDate', |
|
[Permissions] NVARCHAR (MAX) '$.Permissions', |
|
[ResetPasswordKey] VARCHAR (MAX) '$.ResetPasswordKey', |
|
[AccessSecretsManager] BIT '$.AccessSecretsManager' |
|
) |
|
|
|
-- Perform the update |
|
UPDATE |
|
OU |
|
SET |
|
[OrganizationId] = OUI.[OrganizationId], |
|
[UserId] = OUI.[UserId], |
|
[Email] = OUI.[Email], |
|
[Key] = OUI.[Key], |
|
[Status] = OUI.[Status], |
|
[Type] = OUI.[Type], |
|
[ExternalId] = OUI.[ExternalId], |
|
[CreationDate] = OUI.[CreationDate], |
|
[RevisionDate] = OUI.[RevisionDate], |
|
[Permissions] = OUI.[Permissions], |
|
[ResetPasswordKey] = OUI.[ResetPasswordKey], |
|
[AccessSecretsManager] = OUI.[AccessSecretsManager] |
|
FROM |
|
[dbo].[OrganizationUser] OU |
|
INNER JOIN |
|
@OrganizationUserInput OUI ON OU.Id = OUI.Id |
|
|
|
-- Bump account revision dates |
|
INSERT INTO @UserIds |
|
SELECT [UserId] |
|
FROM @OrganizationUserInput |
|
|
|
EXEC [dbo].[User_BumpManyAccountRevisionDates] @UserIds |
|
END |
|
GO
|
|
|