Rock的SQL筆記本

下面這一張圖說明他的需求，他在資料表中的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

rockchang@mails.fju.edu.tw