多语言表扩展字段字段操作

多语言表字段数据读取

if(OBJECT_ID('fn_CombineDisplayName') is not null)
    drop function fn_CombineDisplayName
go

CREATE FUNCTION fn_CombineDisplayName
(
    @CombineName nvarchar(max), -- 多语言表的CombineName
    @Num int -- 第几个扩展字段，私有扩展段+50
)
RETURNS nvarchar(max)
AS
BEGIN
    declare @Result nvarchar(max)=''
    declare @sep varchar(6),@sepLen int,@indexS int
    set @sep='#@#'
    set @sepLen=Len(@sep)
    set @indexS=CHARINDEX(@sep, @CombineName)

    if(@Num=1)
    begin
        set @Result = substring(@CombineName, 1, @indexS - 1)
    end
    else
    begin 
        declare @i int
        set @i=2
        while(@i<@Num)
        begin
            set @indexS=CHARINDEX(@sep, @CombineName, @indexS + @sepLen)
            set @i+=1
        end

        set @indexS = @indexS + @sepLen
        set @Result = substring(@CombineName, @indexS, CHARINDEX(@sep, @CombineName, @indexS) - @indexS)
    end

    return @Result
END
GO

示例

declare @str nvarchar(max)='裁板#@#电建地产-云立方#@#研发类#@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@#1231314#@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@#'
select dbo.fn_CombineDisplayName(@str, 3)

实体多语言表扩展字段写入

-- 实体多语言表扩展字段写入
CREATE OR ALTER PROCEDURE [dbo].[Pro_WriteEntityCombineName]
  @Entity AS BIGINT, -- 实体ID
  @EntityTable AS NVARCHAR(255), -- 实体数据库表名
  @Value AS NVARCHAR(255), -- 待写入的值
  @Index AS INT, -- 字段位置
  @Code AS NVARCHAR(255) = '' -- 值的编码(如果有)
WITH ENCRYPTION
AS
BEGIN

  -- 创建参数临时表
  SELECT
    @Entity AS ID,
    @Code AS Code,
    @Value AS Name,
    'zh-CN' AS SysMLFlag
    INTO #LJH_CombineNameTable

  -- 写入多语言表扩展字段
  EXEC dbo.P_Samuel_CombineNameUpdate @EntityTable,'#LJH_CombineNameTable',@Index -- 公共段：1~50,私有段：51~80

  -- 删除参数临时表
  DROP TABLE #LJH_CombineNameTable
  
END
GO

系统标准功能SQL

-- 同步所有段组合名称
exec dbo.P_BatchSyncFieldCombineName 'UFIDA.U9.CBO.SCM.Item.ItemMaster','adminxdp(adminxdp)';
-- 同步当前段组合名称,Global和Public
exec dbo.P_SyncFieldCombineNameNew 'UFIDA.U9.MO.MO.MOPickList','Global',12,'demo(demo)','1002410310000397','101';