Let’s take everything we’ve discussed about CLR
stored procedures and deployment and apply it to CLR functions. As any
programmer knows, a function is a lot like a procedure, except that it
returns a value (or an object). Mainstream .NET functions typically
return .NET types. SQL CLR functions, on the other hand, must return a SqlType. So to start with, we need to make sure our classes that implement SQL CLR functions import/use the System.Data.SqlTypes namespace. The SQL Server Project template for User Defined Functions contains the appropriate using code by default; you must add the code manually to standard Class Library class code.
Once the namespace is
imported, you can write the functions themselves. In Visual Studio SQL
Server Projects, they should be decorated with the SqlFunctionSqlProcedure counterpart. In our sample code, we will not supply a value for this parameter. SqlFunction
is used by Visual Studio SQL Server projects for deployment of your SQL
CLR functions, but for scalar-valued functions in Class Library
projects it is optional, so it appears in the Class Library sample code
only for our table-valued function (described later). attribute; this attribute accepts an optional name parameter that works identically to its
Listing 1, which shows the code for function fnHelloWorld from fnTest.cs in the sample project, implements a simple “Hello World” function that returns a value of type SqlString.
Listing 1. fnHelloWorld from fnTest.cs
[SqlFunction()]
public static SqlString fnHelloWorld()
{
return new SqlString("Hello World");
}
|
Notice that SqlType
objects require explicit instantiation and constructor value passing;
you cannot simply declare and assign values to them. The code in Listing 2 instantiates a SqlString object inline within the return statement to avoid variable declaration.
A
function that returns a hardcoded value is of little practical use.
Typically, functions are passed values and perform calculations on them,
and they are often used from within T-SQL statements, in effect as
extensions to the functions built into the T-SQL language itself. Listing 2 which shows the code for function fnToCelsius in fnTest.cs in the sample project, implements a Fahrenheit-to-Celsius conversion function.
Listing 2. fnToCelsius from fnTest.cs
[SqlFunction()]
public static SqlDecimal fnToCelsius(SqlInt16 Fahrenheit)
{
return new SqlDecimal((((Int16)Fahrenheit) - 32) / 1.8);
}
|
The function accepts a Fahrenheit temperature (as a SqlInt16), converts it to Celsius, and returns it (as a SqlDecimal). Notice that the code casts the input parameter from a SqlInt16 to a .NET Int16, applies a Fahrenheit-to-Celsius conversion formula, and passes the result to the constructor of a new SqlDecimal object.
Deployment of these
functions is automatic in the Visual Studio SQL Server project version
of our sample code. For the Class Library version, use the T-SQL CREATE FUNCTION command in a similar fashion to our use of the CREATE PROCEDURE command in the previous section, but include a data type specification for the return value. For example, to deploy the fnHelloWorld function, you would use this command:
CREATE FUNCTION fnHelloWorld()
RETURNS NVARCHAR(4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME Chapter03.UserDefinedFunctions.fnHelloWorld
Notice the use of data type NVARCHAR(4000) to correspond with the SqlString type used in the function’s implementation. The WITH EXECUTE AS CALLER clause specifies that the SQL CLR function should execute under the caller’s identity.
Tip
You can enter the CREATE FUNCTION
command yourself, but all such necessary commands for the sample code
SQL CLR functions are contained in the CreateObjects.sql script file in
the Management Studio project supplied with the sample code. |
You can test these
functions using the Visual Studio SQL Server project test script or in
Management Studio. Use the following query in your test script or a
Management Studio query window to test the two functions. (You can also
run the TestScalarFunctions.sql script file in the Management Studio
sample project.)
SELECT
dbo.fnHelloWorld() AS HelloWorld,
dbo.fnToCelsius(212) AS CelsiusTemp
T-SQL functions can return result sets as well as scalar values. Such functions are called table-valued functions
(TVFs). Writing SQL CLR TVFs is possible, although you do so
differently than you would CLR scalar-valued functions or CLR stored
procedures. CLR TVFs must return a type that implements the .NET
interface IEnumerable, and they must declare a “FillRow” method that interprets that type and converts an instance of the type to a table row.
Listing 3-, which shows the code for functions fnPortfolioTable and FillTickerRow in fnTest.cs in the sample project, implements a TVF called fnPortfolioTable.
Listing 3. fnPortfolioTable and FillTickerRow from fnTest.cs
[SqlFunction(
FillRowMethodName="FillTickerRow",
TableDefinition="TickerSymbol nvarchar(5), Value decimal")]
public static System.Collections.IEnumerable fnPortfolioTable(SqlString TickersPacked)
{
string[] TickerSymbols;
object[] RowArr = new object[2];
object[] CompoundArray = new object[3];
char[] parms = new char[1];
parms[0] = ';';
TickerSymbols = TickersPacked.Value.Split(parms);
RowArr[0] = TickerSymbols[0];
RowArr[1] = 1;
CompoundArray[0] = RowArr;
RowArr = new object[2];
RowArr[0] = TickerSymbols[1];
RowArr[1] = 2;
CompoundArray[1] = RowArr;
RowArr = new object[2];
RowArr[0] = TickerSymbols[2];
RowArr[1] = 3;
CompoundArray[2] = RowArr;
return CompoundArray;
}
public static void FillTickerRow(object row, ref SqlString TickerSymbol, ref SqlDecimal
Value)
{
object[] rowarr = (object[])row;
TickerSymbol = new SqlString((string)rowarr[0]);
Value = new SqlDecimal(decimal.Parse(rowarr[1].ToString()));
}
|
Rather than implementing its own IEnumerable-compatible type, fnPortfolioTable uses an array. This is perfectly legal because arrays implement IEnumerable. Function fnPortfolioTable
accepts a semicolon-delimited list of stock ticker symbols and returns a
table with each ticker symbol appearing in a separate row as column
TickerSymbol and a value for the ticker as column Value. The structure
of the returned table is declared in the TableDefinition parameter of the SqlFunction attribute in SQL Server projects and in the CREATE FUNCTION
T-SQL command for Class Library projects. The assigned values are
hardcoded, and only three rows are returned, regardless of how many
ticker symbols are passed in. As with our other samples, this one is
more useful as a teaching tool than as a practical application of TVFs.
Arrays are the name of the game here. First the String.Split
method is used to crack the delimited ticker list into an array of
single ticker strings. Then the TVF structures the data so that each
element in the return value array (CompoundArray) is itself a two-element array storing a single ticker symbol and its value. The function code itself needs only to return CompoundArray. Next, the FillTickerRow function (named in the FillRowMethodName parameter of the SqlFunction
attribute) takes each two-element array and converts its members to
individual scalars that correspond positionally to the columns in the TableDefinition argument of the SqlFunction attribute.
Because the FillRowMethodName parameter of the SqlFunction attribute is required by SQL Server, we have decorated the Class Library version of function fnPortfolioTable
with that attribute, supplying a value for that one parameter. In the
SQL Server project version, we also supply a value for the TableDefinition parameter to enable autodeployment of the TVF.
As with the other
functions, deployment of this function is performed by Visual Studio in
the SQL Server project sample code. For the Class Library version, you
can deploy the function using the following T-SQL command (also
contained in the CreateObjects.sql script file):
CREATE FUNCTION fnPortfolioTable(@TickersPacked [NVARCHAR](4000))
RETURNS TABLE (
TickerSymbol NVARCHAR(5),
VALUE DECIMAL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME Chapter03.UserDefinedFunctions.fnPortfolioTable
As with fnHelloWorld, we have mapped the SqlString data type to an NVARCHAR(4000), this time for one of the input parameters. Because fnPortfolioTable is a TVF, its return type is declared as TABLE, with inline specifications for the table’s definition.
Use the following query
in your Visual Studio test script or a Management Studio query window
to test the TVF (or run the TestTableValuedFunction.sql script file in
the Management Studio sample project):
SELECT * FROM fnPortfolioTable('IBM;MSFT;SUN')
The following data should be returned:
TickerSymbol Value
------------ ------
IBM 1
MSFT 2
SUN 3
