SaveDefinitions.sql

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