【OrBit】TFn_UserCodeToTable（获取用户自定义编码） - .Net 平台标准存储过程&函数&CLR方法

**1.概述**
此“表值函数”返回“用户自定义编码”信息。在获取“用户编码”定义代码信息时，都是通过父级代码去获取代码列表，而且应用极为频繁。为了简化代码，现提供此表值函数，可直接用于查询、表连接使用（相当于视图）、值列表存储过程等。

**2.函数**
2.1、简单查询示例
```sql
SELECT * 
FROM dbo.TFn_UserCode_Table('WES-ProductStore-Type')
--参数可以是“父级编码 ”、“父级编码Id”，建议用“父级编码”
```

![](/media//202201/TFn_UserCodeToTable_1641806294.jpg)

2.2、表连接查询示例
```sql
SELECT * 
FROM    EAM_EqAsset EAT
		LEFT OUTER JOIN EAM_EqAssetType ETP ON EAT.AssetTypeId = ETP.EAM_EqAssetTypeId 
		LEFT OUTER JOIN EAM_EqAssetType RTP ON ETP.RootAssetTypeId = RTP.EAM_EqAssetTypeId  
		LEFT JOIN dbo.AssetCode ON EAT.AssetCodeId = dbo.AssetCode.AssetCodeId
		LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM AssetStates/设备资产状态') AssetStates ON EAT.AssetStateId = AssetStates.FId
		LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM EquipmentRStates/设备运行状态') RStates ON EAT.RStateId = RStates.FId
		LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM SourceType/来源类型') SourceTypes ON EAT.SourceTypeId = SourceTypes.FId
		LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM PlanDimensionT/时间计划维度') Dimensions ON EAT.CycleDimensionId = Dimensions.FId
```

2.3、值列表存储过程示例
```sql
-- =============================================
-- Author:		<OBit HG0064>
-- Create date: <2021.08.10>
-- Description:	<获取 文档类型 列表>
-- Rev:1.00
-- =============================================
ALTER PROCEDURE [dbo].[EAM_DocType_ViewList] 
@QueryParameter NVARCHAR(100) =''
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

SELECT  FId AS DocTypeId,
            FNameDesc AS DocType
    FROM	dbo.UserCodeToTable('DOC Type/文档类别') 
	ORDER BY FName

RETURN 0
END
```