Programming Microsoft SQL Server 2005 : An Overview of SQL CLR - CLR Functions

2/19/2011 3:51:38 PM
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
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
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:

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.


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.)

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
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
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))
TickerSymbol NVARCHAR(5),
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
------------ ------
Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us