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].[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
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.