返回标量CLR自定义函数

昨天有学习了返回表自定义函数《CLR Table-Valued函数》http://www.cnblogs.com/insus/p/4378354.html。今天学习另一个，实现返回标量(Scalar-valued) function。

 
这个标量函数获取分类全名。

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

想把这句写成一个标量函数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存储过程》http://www.cnblogs.com/insus/p/4371762.html：

可复制代码：

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写好的函数效率比较：