傳回标量CLR自定義函數

昨天有學習了傳回表自定義函數《CLR Table-Valued函數》。今天學習另一個，實作傳回标量(Scalar-valued) function。

這個标量函數擷取分類全名。

SELECT [CategoryName] + '--' + [KindName] + '--' + [FruitName] FROM [dbo].Tvf_Fruit() WHERE [Fruit_nbr] = @Fruit_nbr      

View Code

想把這句寫成一個标量函數public static SqlString Tvf_GetFullName(SqlByte fruit_nbr),編寫clr函數，應當使用static關鍵詞。參考資料類型，使用SQL CLR TYPE。

​​

可複制代碼：

[SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString Tvf_GetFullName(SqlByte fruit_nbr)
    {
        SqlConnection connection = new SqlConnection("Context connection=true");
        connection.Open();

        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SELECT [CategoryName] + '--' + [KindName] + '--' + [FruitName] FROM [dbo].Tvf_Fruit() WHERE [Fruit_nbr] = @Fruit_nbr";

        SqlParameter param = new SqlParameter("@Fruit_nbr", SqlDbType.TinyInt);
        param.Value = fruit_nbr;
        command.Parameters.Add(param);

        string rtn = Convert.ToString(command.ExecuteScalar());

        connection.Close();

        return new SqlString(rtn);
    }      

編譯為dll。然後部署至SQL，可執行下面代碼，也可以參考前幾篇的手動在Microsoft SQL Server Management Stuido下進行。更詳細《簡單建立與布署CLR存儲過程》javascript:void(0)：

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Tvf_GetFullName')
    DROP FUNCTION Tvf_GetFullName;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'FruitClr')
    DROP ASSEMBLY FruitClr;
GO

CREATE ASSEMBLY FruitClr
FROM 'E:\FruitClr.dll' 
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION Tvf_Fruit()
RETURNS TABLE (
    Fruit_nbr TINYINT,
    FruitCategory_nbr TINYINT,
    CategoryName NVARCHAR(30),
    FruitKind_nbr TINYINT,
    KindName NVARCHAR(30),
    FruitName NVARCHAR(30)
)
AS
EXTERNAL NAME [FruitClr].UserDefinedFunctions.Tvf_Fruit;
GO

CREATE FUNCTION Tvf_GetFullName(@Fruit_nbr tinyint) 
RETURNS NVARCHAR(MAX) 
AS EXTERNAL NAME [FruitClr].UserDefinedFunctions.Tvf_GetFullName; 
GO      

執行結果：

下面可對比一下普通的SQL語句與Clr寫好的函數效率比較：