how to get Microsoft Dynamics CRM Option set values in SQL Server

Description:-

In this example we explain that how to get Microsoft Dynamics CRM Option set values in SQL Server. There are many question are generated in our mind like where does Microsoft Dynamics CRM store Option Set value in SQL Server?

Or SQL Query to retrieve text of the Option Set of the Dynamics CRM or Get Entity Option set values in Dynamics CRM 2011/2015 etc.

SQL Query to get the option set Label Name of Microsoft Dynamics CRM.

To above all question the solution is that these are stored in StringMapBase table. You will just query via object type code of the entity, option set value and language and that will give you the value of the attribute. Below is query for get Option set text or display label in SQL Server query

Query:-

1) SELECT

      [AttributeName]

      ,[AttributeValue]

      ,[LangId]

      ,[OrganizationId]

      ,[Value]

      ,[DisplayOrder]

      ,[VersionNumber]

      ,[StringMapId]

  FROM [CRM2015_MSCRM].[dbo].StringMap where Value='Advertisement'

2) select Value from StringMapBase  where AttributeName='LeadSourceCode' and AttributeValue=1  AND LangId=1033 and ObjectTypeCode=4

or you can also create function for that like

CREATE FUNCTION fn_GetStringMapValue

(

    @AttributeName nvarchar(100),

    @AttributeValue int

)

RETURNS nvarchar(4000)

AS

BEGIN

    DECLARE @Result nvarchar(4000)

    SELECT @Result = Value

    FROM dbo.FilteredStringMap

    WHERE AttributeName = @AttributeName AND AttributeValue = @AttributeValue

    RETURN @Result

END