Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [xdb_refdata].[SaveDefinitions]
(
@Definitions [xdb_refdata].[MonikerDefinitions] READONLY
)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@Results [xdb_refdata].[DefinitionResults],
@CurrentDate DATETIME2(0) = CONVERT(DATETIME2(0), GETUTCDATE()),
@DefinitionInstanceID UNIQUEIDENTIFIER,
@Moniker NVARCHAR(MAX),
@TypeID UNIQUEIDENTIFIER,
@ID UNIQUEIDENTIFIER,
@Version SMALLINT,
@IsActive BIT,
@DataTypeRevision SMALLINT,
@Data VARBINARY(MAX),
@IsUpdate BIT,
@Hash BINARY(32)
DECLARE InputDefinition CURSOR FOR
SELECT [DefinitionInstanceID], [Moniker], [TypeID], [Version], [IsActive], [DataTypeRevision], [Data] FROM @Definitions
OPEN InputDefinition
FETCH NEXT FROM InputDefinition
INTO @DefinitionInstanceID, @Moniker, @TypeID, @Version, @IsActive, @DataTypeRevision, @Data
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @IsUpdate = 0
BEGIN TRANSACTION [MonikerID]
/*
This query was commented as it runs slow and take around 3 min to return data.
It has been replaced by query block below this block.
SET @ID = [xdb_refdata].[GetDefinitionID](@Moniker, @TypeID)
*/
/*New code block to get definition id*/
SET @Hash = [xdb_refdata].[GetDefinitionMonikerHash](@Moniker)
SELECT TOP 1
@ID = [xdb_refdata].[DefinitionMonikers].[ID]
FROM
[xdb_refdata].[DefinitionMonikers]
INNER JOIN
[xdb_refdata].[Definitions]
ON
[xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
WHERE
[xdb_refdata].[DefinitionMonikers].[MonikerHash] = @Hash AND
[xdb_refdata].[DefinitionMonikers].[Moniker] = @Moniker AND
[xdb_refdata].[Definitions].[TypeID] = @TypeID
IF(@ID IS NULL)
BEGIN
SET @ID = NEWID()
INSERT INTO
[xdb_refdata].[DefinitionMonikers]
(
[ID],
[Moniker],
[MonikerHash]
)
VALUES
(
@ID,
@Moniker,
[xdb_refdata].[GetDefinitionMonikerHash](@Moniker)
)
END
COMMIT TRANSACTION [MonikerID]
IF EXISTS ( SELECT 1 FROM [xdb_refdata].[Definitions] WITH (UPDLOCK) WHERE [ID] = @ID AND [Version] = @Version )
BEGIN
UPDATE [xdb_refdata].[Definitions] WITH (UPDLOCK) SET
[TypeID] = @TypeID,
[IsActive] = @IsActive,
[LastModified] = @CurrentDate,
[DataTypeRevision] = @DataTypeRevision,
[Data] = @Data
WHERE
[ID] = @ID AND
[Version] = @Version
SET @IsUpdate = 1
END
ELSE
BEGIN
INSERT INTO [xdb_refdata].[Definitions] WITH (UPDLOCK)
(
[ID],
[Version],
[TypeID],
[IsActive],
[LastModified],
[DataTypeRevision],
[Data]
)
VALUES
(
@ID,
@Version,
@TypeID,
@IsActive,
@CurrentDate,
@DataTypeRevision,
@Data
)
END
INSERT INTO @Results
(
[DefinitionInstanceID],
[Moniker],
[TypeID],
[Version],
[Success],
[Message]
)
VALUES
(
@DefinitionInstanceID,
@Moniker,
@TypeID,
@Version,
1,
CASE
WHEN @IsUpdate = 1
THEN 'UPDATE'
ELSE
NULL
END
)
END TRY
BEGIN CATCH
INSERT INTO @Results
(
[DefinitionInstanceID],
[Moniker],
[TypeID],
[Version],
[Success],
[Message]
)
VALUES
(
@DefinitionInstanceID,
@Moniker,
@TypeID,
@Version,
0,
ERROR_MESSAGE()
)
END CATCH
FETCH NEXT FROM InputDefinition
INTO @DefinitionInstanceID, @Moniker, @TypeID, @Version, @IsActive, @DataTypeRevision, @Data
END
CLOSE InputDefinition
DEALLOCATE InputDefinition
SELECT
[DefinitionInstanceID],
[Moniker],
[TypeID],
[Version],
[Success],
[Message]
FROM @Results
AS Results
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.