首页 > 数据库技术 > 详细

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

时间:2020-07-28 10:54:30      阅读:86      评论:0      收藏:0      [点我收藏+]

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

查询结果如下图:

 技术分享图片

下面是对应的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查询时,如何显示选中的选项名称

原文:https://www.cnblogs.com/parkerchen/p/13388661.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!