GetDefinitions.sql

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [xdb_refdata].[GetDefinitions]
(
    @Criteria [xdb_refdata].[DefinitionCriteria] READONLY,
    @LatestActiveOnly BIT,
    -- Used for selecting only one of the result sets. 1 = Definitions, 2 = DefinitionCultures, anything else = both
    @ResultSetSelector SMALLINT = 0
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    IF @LatestActiveOnly IS NULL
    BEGIN
        RAISERROR('Parameter @LatestActiveOnly is NULL', 16, 1) WITH NOWAIT;
        RETURN
    END

    DECLARE
        @MatchingDefinitions [xdb_refdata].[DefinitionBatch]

    DECLARE @MappedCriteria TABLE
    (
        [ID] UNIQUEIDENTIFIER,
        [Version] SMALLINT,
        [Culture] VARCHAR(84)
    )

    -- Resolve IDs for monikers
	/*
	This query was commented as it runs slow and take around 1 min to return data.
	It has been replaced by query block below this block.
    INSERT INTO @MappedCriteria
    SELECT
        [xdb_refdata].[GetDefinitionID]([Moniker], [TypeID]),
        [Version],
        [Culture]
    FROM
        @Criteria
	*/

	/*New code block to get definition id*/
	DECLARE @Hash BINARY(32)

	SELECT
        @Hash = [xdb_refdata].[GetDefinitionMonikerHash]([Moniker])
	FROM @Criteria

    INSERT INTO @MappedCriteria
    SELECT TOP 1
        [xdb_refdata].[DefinitionMonikers].[ID],
		c.[Version],
		c.[Culture]
    FROM
        [xdb_refdata].[DefinitionMonikers]
    INNER JOIN
        [xdb_refdata].[Definitions]
    ON
        [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
	INNER JOIN @Criteria c
	ON
		[xdb_refdata].[DefinitionMonikers].[Moniker] = c.Moniker AND
        [xdb_refdata].[Definitions].[TypeID] = c.TypeID
    WHERE
        [xdb_refdata].[DefinitionMonikers].[MonikerHash] = @Hash 

    -- Populate latest versions where no specific version was supplied
    IF @LatestActiveOnly = 1
    BEGIN
        UPDATE @MappedCriteria
        SET
            [Version] = MaxVersion
            FROM
            (
                SELECT
                    MAX([Version]) AS MaxVersion,
                    [ID]
                FROM
                    [xdb_refdata].[Definitions]
                WHERE
                    [Definitions].[ID] = [ID] AND
                    [Definitions].[IsActive] = 1
                GROUP BY
                    [ID]
            ) MaxVersions
            INNER JOIN
                @MappedCriteria AS [MappedCriteria]
            ON
                [MappedCriteria].[ID] = MaxVersions.[ID]
        WHERE [Version] IS NULL
    END
    ELSE
    BEGIN
        INSERT INTO @MappedCriteria
        (
            [ID],
            [Version],
            [Culture]
        )
            SELECT DISTINCT
                [MappedCriteria].[ID],
                [xdb_refdata].[Definitions].[Version],
                [MappedCriteria].[Culture]
            FROM
                [xdb_refdata].[Definitions]
            INNER JOIN 
                @MappedCriteria AS [MappedCriteria]
            ON
                [xdb_refdata].[Definitions].ID = [MappedCriteria].ID
            WHERE
                [MappedCriteria].[Version] IS NULL

        DELETE @MappedCriteria
        WHERE
            [Version] IS NULL
    END

    -- Find matching definitions
    INSERT INTO
        @MatchingDefinitions
    (
        [ID],
        [TypeID],
        [Version],
        [IsActive],
        [LastModified],
        [DataTypeRevision],
        [Data]
    )
    SELECT DISTINCT
        [xdb_refdata].[Definitions].[ID],
        [xdb_refdata].[Definitions].[TypeID],
        [xdb_refdata].[Definitions].[Version],
        [xdb_refdata].[Definitions].[IsActive],
        [xdb_refdata].[Definitions].[LastModified],
        [xdb_refdata].[Definitions].[DataTypeRevision],
        [xdb_refdata].[Definitions].[Data]
    FROM
        [xdb_refdata].[Definitions]
    INNER JOIN
        @MappedCriteria [Criteria]
    ON
        [Criteria].[ID] = [xdb_refdata].[Definitions].[ID] AND
        [Criteria].[Version] = [xdb_refdata].[Definitions].[Version]

    IF @ResultSetSelector IS NULL OR @ResultSetSelector != 2
    BEGIN
        SELECT
            [xdb_refdata].[GetDefinitionMoniker]([MatchingDefinitions].[ID]) AS [Moniker],
            [TypeID],
            [Version],
            [IsActive],
            [LastModified],
            [DataTypeRevision],
            [Data]
        FROM
            @MatchingDefinitions AS [MatchingDefinitions]
        ORDER BY
            [Moniker]
    END

    IF @ResultSetSelector IS NULL OR @ResultSetSelector != 1
    BEGIN
        -- Find cultures for definitions
        SELECT
            [xdb_refdata].[GetDefinitionMoniker]([MatchingDefinitions].[ID]) AS [Moniker],
            [MatchingDefinitions].[TypeID],
            [MatchingDefinitions].[Version],
            [xdb_refdata].[DefinitionCultures].[Culture],
            [xdb_refdata].[DefinitionCultures].[Data]
        FROM
            @MatchingDefinitions AS [MatchingDefinitions]
        INNER JOIN
            [xdb_refdata].[DefinitionCultures]
        ON
            [xdb_refdata].[DefinitionCultures].[ID] = [MatchingDefinitions].[ID] AND
            [xdb_refdata].[DefinitionCultures].[Version] = [MatchingDefinitions].[Version]
        INNER JOIN
            @MappedCriteria [Criteria]
        ON
            [Criteria].[ID] = [xdb_refdata].[DefinitionCultures].[ID] AND
            [Criteria].[Version] = [xdb_refdata].[DefinitionCultures].[Version]
        WHERE
            [Criteria].[Culture] IS NULL OR
            [Criteria].[Culture] = [xdb_refdata].[DefinitionCultures].[Culture]
    END
END
GO