# 简介
我最初想着从 **==函数==** 动态获取仓位名称，但是运行函数提示运算符EXECUTE STRING 无效，在function中不能使用exec和sp_executesql。
所以做了一个获取仓位名称的 **==存储过程==**
**传送门：[MSSQL - 仓位的标识【动态】获取仓位名称](https://wenku.my7c.com/article/483211870225238784?productLineId=1)**
但是使用存储过程
局限性较大，无法直接在查询中调用，为了解决这个问题决定使用 **
CLR 用户定义函数 ** 来实现
# 配置 SQL Server CLR
``` SQL
---开启所有服务器配置选项
EXEC sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE
```
``` SQL
--开启clr enabled 选项
EXEC sp_configure N'clr enabled', N'1'
RECONFIGURE WITH OVERRIDE
```
``` SQL
--关闭所有服务器配置选项
EXEC sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
```
## 假如存在权限异常
**
如果存在权限问题，执行下面一段脚本**
``` sql
ALTER DATABASE 数据库名 SET TRUSTWORTHY ON
EXEC sp_changedbowner 'sa'
```
# 创建CLR函数 VS2022
## 1. 创建一个数据库项目
## 2. 新建一个用户自定义函数
## 3. 根据上一篇文章存储过程改为 C# 代码
``` C#
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString ScalarFunction(SqlInt32 FSTOCKID, SqlInt32 FSTOCKLOCID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
DataTable T_BD_STOCKFLEXITEM = new DataTable();
string Str = $"SELECT FSEQ, T31.FFLEXNUMBER FROM T_BD_STOCKFLEXITEM T1 INNER JOIN T_BAS_FLEXVALUES T31 ON T1.FFLEXID = T31.FID WHERE FSTOCKID = {FSTOCKID} ";
SqlCommand cmd = new SqlCommand(Str, conn);
var edr = cmd.ExecuteReader();
DataTable schemaTable = edr.GetSchemaTable();
try
{
//动态构建表，添加列
foreach (DataRow dr in schemaTable.Rows)
{
DataColumn dc = new DataColumn();
//设置列的数据类型
dc.DataType = dr[0].GetType();
//设置列的名称
dc.ColumnName = dr[0].ToString();
//将该列添加进构造的表中
T_BD_STOCKFLEXITEM.Columns.Add(dc);
}
if (edr.HasRows)
{
//读取数据添加进表中
while (edr.Read())
{
DataRow row = T_BD_STOCKFLEXITEM.NewRow();
//填充一行数据
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
row[i] = edr[i].ToString();
}
T_BD_STOCKFLEXITEM.Rows.Add(row);
row = null;
}
edr.Close();
schemaTable = null;
}
}
catch (Exception ex)
{
if (!edr.IsClosed) { edr.Close(); };
//抛出异常
throw new Exception(ex.Message);
}
string ReturnStr = "";
if (T_BD_STOCKFLEXITEM != null&& T_BD_STOCKFLEXITEM.Rows.Count>0)
{
foreach (DataRow item in T_BD_STOCKFLEXITEM.Rows)
{
string FFLEXNUMBER_SQL = $"SELECT V{item["FFLEXNUMBER"].ToString()}.FNAME FROM T_BAS_FLEXVALUESDETAIL FVD LEFT JOIN T_BAS_FLEXVALUESENTRY_L AS V{item["FFLEXNUMBER"].ToString()} ON FVD.{item["FFLEXNUMBER"].ToString()}=V{item["FFLEXNUMBER"].ToString()}.FENTRYID AND V{item["FFLEXNUMBER"].ToString()}.FLOCALEID = 2052 WHERE FID={FSTOCKLOCID}";
cmd = new SqlCommand(FFLEXNUMBER_SQL, conn);
string OutPutStr = (string) cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(OutPutStr))
{
ReturnStr = $"{ReturnStr}.{OutPutStr}";
}
}
}
return ReturnStr.Trim('.');
}
return new SqlString("获取失败");
}
```
# 注册 CLR 程序集
## 注册CLR程序集方式一，指定程序集DLL的路径
``` sql
--注册CLR程序集方式一，指定程序集DLL的路径
CREATE ASSEMBLY UserDefinedClrAssembly
--AUTHORIZATION sa --指定数据库所有者，默认为当前用户
FROM 'D:\项目\SQLSERVER.CLR.ScalarFunction\bin\Debug\SQLSERVER.CLR.ScalarFunction.dll' --指定文件路径
WITH PERMISSION_SET = UNSAFE; --指定程序集的权限
--SAFE：无法访问外部系统资源；
--EXTERNAL_ACCESS：可以访问某些外部系统资源；
--UNSAFE：可以不受限制的访问外部系统资源
GO
```
## 注册CLR程序集方式二，指定程序集DLL的16进制文件流
**==第二种，这种方式注册程序集稍微复杂一些，但是好处就是注册成功之后，可以移动甚至删除DLL文件，只要不是变更迁移数据库，都不用重新注册。==**
``` sql
--注册CLR程序集方式二，指定程序集DLL的16进制文件流
CREATE ASSEMBLY [SQLSERVER.CLR.ScalarFunction] AUTHORIZATION [dbo]
FROM 0x4D5A9000030000000400000--.....此处省略数据太长
WITH PERMISSION_SET = UNSAFE
```
## 从VS获取注册程序集代码【方式二】
# 注册、删除 标值函数/程序集
``` sql
--注册标值函数
CREATE FUNCTION [dbo].Mindee_CLR_GetFStockLOCName (@FSTOCKID [int], @FSTOCKLOCID [int])
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [SQLSERVER.CLR.ScalarFunction].[UserDefinedFunctions].[ScalarFunction];
GO
--删除标量函数 Mindee_CLR_GetFStockLOCName
DROP FUNCTION dbo.Mindee_CLR_GetFStockLOCName
GO
--删除程序集 [SQLSERVER.CLR.ScalarFunction]
DROP ASSEMBLY [SQLSERVER.CLR.ScalarFunction]
GO
```
# 运行测试
## 测试SQL
``` sql
SELECT
T1.FBASEQTY AS '库存量(基本单位)',
t1.FBASEAVBQTY AS '可用量(基本单位)'
,T3.FNUMBER 物料编码,
T3L.FNAME as 物料名称,
T4L.FNAME 物料分组名称,
T3L.FDESCRIPTION as 物料描述,
T3L.FSPECIFICATION AS 规格型号
,T2L.FNAME as 仓库名称
,dbo.Mindee_CLR_GetFStockLOCName(T1.FSTOCKID,T1.FSTOCKLOCID) as 仓位名称 --自定义CLR函数
--,T1.FSTOCKID AS 仓库标识
--,T1.FSTOCKLOCID AS 仓位标识
,T5L.FNAME AS 基本单位
,T1.FLOT AS 批号基本标识
,T6L.FNUMBER AS 批号
from T_STK_INVENTORY t1
INNER JOIN t_BD_Stock T2 ON T1.FSTOCKID=T2.FSTOCKID
INNER JOIN t_BD_Stock_l T2L ON T1.FSTOCKID=T2L.FSTOCKID and T2L.FLOCALEID=2052
INNER JOIN T_BD_MATERIAL T3 ON T1.FMaterialId = T3.FMasterId AND T1.FSTOCKORGID=T3.FUSEORGID
INNER JOIN T_BD_MATERIAL_L T3L ON T3.FMaterialId=T3L.FMaterialId AND T3L.FLOCALEID=2052
LEFT JOIN T_BD_MATERIALGROUP T4 ON T4.FID=T3.FMATERIALGROUP
LEFT JOIN T_BD_MATERIALGROUP_L T4L ON T4L.FID=T4.FID AND T4L.FLOCALEID=2052
INNER JOIN T_BD_UNIT_L T5L ON T5L.FUNITID=t1.FBASEUNITID AND T5L.FLOCALEID=2052
LEFT JOIN T_BD_LOTMASTER T6L ON T6L.FLOTID=T1.FLOT
WHERE T1.FBASEQTY != 0
```
## 效果
完美 ~
# 相关参考连接
[SQL Server CLR 使用 C# 自定义函数](https://www.cnblogs.com/Brambling/p/8000911.html)
[CLR 标量值函数](https://learn.microsoft.com/zh-cn/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-scalar-valued-functions?view=sql-server-ver16)
___
==如有错误之处，敬请指正。==
