Returns a scalar CLR custom function

I learned yesterday. Returns a table Custom function "CLR table-valued function" http://www.cnblogs.com/insus/p/4378354.html. Learn another today to implement the return scalar (scalar-valued) function.

This scalar function gets the full name of the class.

SELECT [CategoryName] + '--' + [Kindname] + '--' + [Fruitname]  from [dbo]. Tvf_fruit ()WHERE [FRUIT_NBR] = @Fruit_nbr

View Code

To write this sentence into a scalar function public static SqlString tvf_getfullname (SqlByte FRUIT_NBR), writing CLR functions, you should use the static keyword. Reference data type, using SQL CLR type.


To copy code:

[SqlFunction (DataAccess =Dataaccesskind.read)] Public StaticSqlString tvf_getfullname (SqlByte fruit_nbr) {SqlConnection connection=NewSqlConnection ("Context connection=true"); Connection.        Open (); SqlCommand Command=NewSqlCommand (); Command. Connection=connection; Command.commandtext="SELECT [CategoryName] + '--' + [kindname] + '--' + [fruitname] from [dbo]. Tvf_fruit () WHERE [FRUIT_NBR] = @Fruit_nbr"; SqlParameter param=NewSqlParameter ("@Fruit_nbr", Sqldbtype.tinyint); Param. Value=FRUIT_NBR; Command.        Parameters.Add (param); stringRTN =convert.tostring (command.        ExecuteScalar ()); Connection.        Close (); return NewSqlString (RTN); }

View Code

Compile as a DLL. Then deploy to SQL, execute the code below, or refer to the previous manual under Microsoft SQL Server Management stuido. In more detail, "Simple creation and deployment of the CLR stored procedures" http://www.cnblogs.com/insus/p/4371762.html:

To copy code:

IF EXISTS(SELECT *  fromSys.objectsWHEREName= 'Tvf_getfullname')    DROP FUNCTIONTvf_getfullname;GOIF EXISTS(SELECT *  fromSys.assembliesWHEREName= 'FRUITCLR')    DROPASSEMBLY fruitclr;GOCREATEASSEMBLY fruitclr from 'E:\FruitClr.dll'  withPermission_set=SAFE;GOCREATE FUNCTIONtvf_fruit ()RETURNS TABLE(FRUIT_NBRTINYINT, Fruitcategory_nbrTINYINT, CategoryNameNVARCHAR( -), Fruitkind_nbrTINYINT, KindnameNVARCHAR( -), FruitnameNVARCHAR( -)) asEXTERNAL NAME[FRUITCLR]. Userdefinedfunctions.tvf_fruit;GOCREATE FUNCTIONTvf_getfullname (@Fruit_nbr tinyint) RETURNS NVARCHAR(MAX)  asEXTERNAL NAME[FRUITCLR]. Userdefinedfunctions.tvf_getfullname; GO

View Code

Execution Result:

Here's a comparison of common SQL statements with CLR-written function efficiency comparisons:

Returns a scalar CLR custom function