返回标量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;GOIF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'FruitClr')    DROP ASSEMBLY FruitClr;GOCREATE ASSEMBLY FruitClrFROM 'E:/FruitClr.dll' WITH PERMISSION_SET = SAFE;GOCREATE FUNCTION Tvf_Fruit()RETURNS TABLE (    Fruit_nbr TINYINT,    FruitCategory_nbr TINYINT,    CategoryName NVARCHAR(30),    FruitKind_nbr TINYINT,    KindName NVARCHAR(30),    FruitName NVARCHAR(30))ASEXTERNAL NAME [FruitClr].UserDefinedFunctions.Tvf_Fruit;GOCREATE FUNCTION Tvf_GetFullName(@Fruit_nbr tinyint) RETURNS NVARCHAR(MAX) AS EXTERNAL NAME [FruitClr].UserDefinedFunctions.Tvf_GetFullName; GO

执行结果：

下面可对比一下普通的SQL语句与Clr写好的函数效率比较：