SQL CLR初体验,利用CLR撰写SQL Function处理计算公式并回传计算结果

今天在SQL PASS的FB上有一位朋友询问一个问题

如何让SQL根据Column中保存的算式,在Select中算出每一笔Row的答案


下面这一张图说明他的需求,他在数据表中的CalCond保存了每一个ROW的算式。然后他要将InvSubTot当参数带入公式后让SQL直接算出答案。

一开始我想这得透过Function来做,因此我的作法为建立一个Function然后将算式CalcStr丢进去再利用Exec sp_executesql来执行该公式。写法如下

CREATE FUNCTION [dbo].[fn_math]
(
	@Para varchar(100)
)
RETURNS int
AS
BEGIN
	Declare @Return int
	exec sp_executesql @Para, @Return output;
	RETURN @Return;
END

GO

但事与愿违,执行解果发生错误(如下图所示),原因是因为SQL不允许Function中去Call Stored Procedure。因此这一个方法是行不通了。

我爬了一堆关于这需求的解法,大多都是写Stored Procedure来做,但User是希望一次Select后就可以取得他所要的结果。因此既然SQL没有这类Function的支持,那我们就自己写一个吧。

我们会利用SQL CLR的功能来写一个对象负责算出结果。CLR是Common Language Runtime的缩写,简单的说就是我们可以根据需求透过.NET  Framework设计出各种数据库的对象,其中包括预存程序、使用者自订函数、触发程序、使用者自订类型以及使用者自订汇总函数等功能。

下面步骤是用Visual Studio建立CLR的作法,首先建立一个数据库项目。

在新开启的项目中新增一个项目。

我们选择SQL CLR C#并点选SQL CLR C# 使用者定义函数。

此时项目会开启一个cs档,默认名称是SqlFunction1.cs,当然您可以依照自己需求重新命名,在此我就懒得改了。而下图的Code就是将算式字符串算出结果的Code,Rock是爬文爬到的(我不太会写C#,大家参考就好)。

写完后一定要建置项目。

编译完成后,此时我们浏览项目数据夹,会看见一个用项目名称命名的dll档,本范例中项目是Database6,因此可以在下图中看见一Database6.dll档。

完成上述步骤后我们就已经建立一个我们需求的CLR了。接下来就是如何将它布署到SQL Server上。

默认中SQL的CLR功能是Disable,因此我们得先启用CLR功能,语法如下:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

而CLR的程序有可能会捞取SQL外部资源,例如,文件、网络、环境变量或登录等。因此如果有这种需求那该数据库也得开启TrustWorthy的功能。语法如下:

Alter Database DB1 Set TrustWorthy ON;

完成上面两个步骤后,我们来将该dll导入SQL吧。如下图所示,我们要新增一个组件(ASSEMBLY)。

我们在新增组件地UI中按下浏览按钮,并选取我们刚刚建立的Database6.dll文件。

选取完成后,如下图示该组件默认名称就是Database6,而且不能修改。

当我们在上一个步骤完成新增组件后,就可以看见该DB中产生了一个名为Database6的组件(如下图所示)。

完成组件建立后我们该怎么用呢 ? 基本上我们要用Function去Call该组件。SQL建立Function的语法如下图TSQL。我们建立了一个名为fn_math2的Function,该Function是引用我们刚刚建的组件Database6。

完成上述步骤后,我们可以看见SQL增加了一个fn_math2的函数了,接下来我们就看看fn_math2函数是否能达到我们的需求。

下图中我们在Select中用到了fn_math2这一个函数,我们将算式丢进该函数,而该函数也正确的回传了计算的结果,因此这样的做法是可以符合该USER的需求。

参考数据来源

CREATE ASSEMBLY

SQL Server CLR 整合简介

我是ROCK

[email protected]