Dynamics 365 V9.0 多选选项集之SQL查询

1.CRM 9.0版本引入了一个新的数据类型：多选选项集。多选项集数据类型，新建方式和建选项集字段一样，只是数据类型那选择的是多选选项集

 

2.多选项集字段在数据库存储的是字符串数组

3.查询结果

 4.SQL查询语句

select
    mt.name
from 实体名称
OUTER APPLY fn_GetPickListNameByMultiple('实体名称','字段名',字段名,2052) mt

5.fn_GetPickListNameByMultiple

 

CREATE FUNCTION [dbo].[fn_GetPickListNameByMultiple] 
(    
    @entityName NVARCHAR(200)  -- 实体名称
, @fieldName NVARCHAR(200)     -- 字段名称
, @fieldValue NVARCHAR(200)    -- 字段值
, @langId int                --  语言code
)
RETURNS @MultiTable TABLE 
(
[name] nvarchar(max)
)
AS
begin
    DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     SELECT @CurrentIndex=1;
     DECLARE @Text nvarchar(max);
     DECLARE @ReturnText nvarchar(max)
     DECLARE @value nvarchar(max)
     set @ReturnText='';
     set @value='';
     set @fieldValue=replace(replace(@fieldValue,'[',''),']','')
     WHILE(@CurrentIndex<=len(@fieldValue))
         BEGIN
             SELECT @NextIndex=charindex(',',@fieldValue,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@fieldValue)+1;
                 SELECT @Text=substring(@fieldValue,@CurrentIndex,@NextIndex-@CurrentIndex);
                 if(@Text<>-1)
                 begin
                    SELECT DISTINCT 
                          @value=sm.value
                     FROM   entity e
                     INNER JOIN stringmap sm
                            ON e.objecttypecode = sm.objecttypecode AND
                               sm.attributename = @fieldName AND sm.AttributeValue = @Text
                               AND sm.LangId=@langId
                     WHERE  e.OverwriteTime=0 AND e.Name = @entityName
                     set @ReturnText=@ReturnText+@value+',';
                 end
                 SELECT @CurrentIndex=@NextIndex+1;
             END
             if(isnull(@fieldValue,'')='')
             begin
                INSERT INTO @MultiTable([name]) VALUES(null)
             end
             else
             begin
                INSERT INTO @MultiTable([name]) VALUES(substring(@ReturnText,1,len(@ReturnText)-1))
             end
RETURN 
end
GO

 

 

注：如有更好的查询方式，请指教，谢谢！