枚举信息获取

-- 枚举信息获取
CREATE FUNCTION [dbo].[LJH_GetEnumCodeNameValue]
( @EnumName AS nvarchar(255),
  @Value AS nvarchar(255),
  @SetStr AS nvarchar(255) = '',
  @GetStr AS nvarchar(255) = '',
  @Language NVARCHAR(5) = 'zh-CN'
)
RETURNS nvarchar(255)
WITH ENCRYPTION
AS
BEGIN
    SET @Language = ISNULL(@Language,'zh-CN')
    IF ISNULL(@SetStr,'') = '' OR ISNULL(@GetStr,'') = ''
        BEGIN
            SET @SetStr = 'V'
            SET @GetStr = 'C'
        END
    DECLARE @Ret nvarchar(255) = ''
    IF ISNULL(@Value,'') != ''
        BEGIN
            IF @SetStr = 'V' AND @GetStr = 'N' -- 根据值获取名称
                SELECT TOP 1 @Ret = isnull(A.Name,'') FROM UBF_Sys_ExtEnumValue_Trl AS A INNER JOIN UBF_Sys_ExtEnumValue AS B ON A.ID =B.ID INNER JOIN UBF_Sys_ExtEnumType AS C ON C.ID =B.ExtEnumType WHERE A.SysMLFlag = @Language AND C.Code = @EnumName AND B.EValue = @Value
            ELSE IF @SetStr = 'V' AND @GetStr = 'C' -- 根据值获取编码
                SELECT TOP 1 @Ret = isnull(B.Code,'') FROM UBF_Sys_ExtEnumValue_Trl AS A INNER JOIN UBF_Sys_ExtEnumValue AS B ON A.ID =B.ID INNER JOIN UBF_Sys_ExtEnumType AS C ON C.ID =B.ExtEnumType WHERE A.SysMLFlag = @Language AND C.Code = @EnumName AND B.EValue = @Value
            ELSE IF @SetStr = 'C' AND @GetStr = 'V' -- 根据编码获取值
                SELECT TOP 1 @Ret = isnull(B.EValue,'') FROM UBF_Sys_ExtEnumValue_Trl AS A INNER JOIN UBF_Sys_ExtEnumValue AS B ON A.ID =B.ID INNER JOIN UBF_Sys_ExtEnumType AS C ON C.ID =B.ExtEnumType WHERE A.SysMLFlag = @Language AND C.Code = @EnumName AND B.Code = @Value
            ELSE IF @SetStr = 'C' AND @GetStr = 'N' -- 根据编码获取名称
                SELECT TOP 1 @Ret = isnull(A.Name,'') FROM UBF_Sys_ExtEnumValue_Trl AS A INNER JOIN UBF_Sys_ExtEnumValue AS B ON A.ID =B.ID INNER JOIN UBF_Sys_ExtEnumType AS C ON C.ID =B.ExtEnumType WHERE A.SysMLFlag = @Language AND C.Code = @EnumName AND B.Code = @Value
            ELSE IF @SetStr = 'N' AND @GetStr = 'C' -- 根据名称获取编码
                SELECT TOP 1 @Ret = isnull(B.Code,'') FROM UBF_Sys_ExtEnumValue_Trl AS A INNER JOIN UBF_Sys_ExtEnumValue AS B ON A.ID =B.ID INNER JOIN UBF_Sys_ExtEnumType AS C ON C.ID =B.ExtEnumType WHERE A.SysMLFlag = @Language AND C.Code = @EnumName AND A.Name = @Value
            ELSE IF @SetStr = 'N' AND @GetStr = 'V' -- 根据名称获取值
                SELECT TOP 1 @Ret = isnull(B.EValue,'') FROM UBF_Sys_ExtEnumValue_Trl AS A INNER JOIN UBF_Sys_ExtEnumValue AS B ON A.ID =B.ID INNER JOIN UBF_Sys_ExtEnumType AS C ON C.ID =B.ExtEnumType WHERE A.SysMLFlag = @Language AND C.Code = @EnumName AND A.Name = @Value
        END
    RETURN @Ret
END