Dynamics 365 V9.0版本后引入多选项集,SQL查询时,如何显示选中的选项名称

SQL查询语句如下:

select new_name,new_code,new_multipleselection,multipleselection.name 
from new_attachment
outer apply fn_GetPickListNameByMultiple(new_attachment,new_multipleselection,new_multipleselection,1033)  multipleselection

查询结果如下图:

 Dynamics 365 V9.0版本后引入多选项集,SQL查询时,如何显示选中的选项名称

下面是对应的SP,直接在我们数据库执行后,sql就可以直接使用功能:

USE [***_MSCRM]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[fn_GetPickListNameByMultiple] 
(
  @entityName NVARCHAR(200)
, @fieldName NVARCHAR(200)
, @fieldValue NVARCHAR(200)
, @langId int
)
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

 

Dynamics 365 V9.0版本后引入多选项集,SQL查询时,如何显示选中的选项名称

上一篇:使用 Codec Engine 的 API 函数(四)


下一篇:SQL语言之DML