Professional SQL Server™ 2005 Programming

14.6. Creating Table-Valued Functions

Functions are going to continue to be a focus of this chapter for a bit. Why? Well, functions have a few more twists to them than some of the other assembly uses.

In this section, we're going to focus in on table valued functions. They are among the more complex things we need to deal with in this chapter, but, as they are in the T-SQL version, they were also among the more powerful. The uses range far and wide. They can be as simple as special treatment of a column in something you could have otherwise done in a typical T-SQL function or can be as complex as a merge of data from several disparate and external datasources.

Go ahead and start another Visual Studio project called ExampleTVF, using the SQL Server project template — also add a new user-defined function. We're going to be demonstrating accessing the file system this time, so add the following references:

using System;
using System.IO;
using System.Collections;
using Microsoft.SqlServer.Server;

Before we get too much into the code, let's look ahead a bit at some of the things a table-valued function — or TVF — requires:

The entry function must implement the IEnumerable interface. This is a special, widely used, interface in .NET that essentially allows for the iteration over some form of row (be it in an array, collection, table, or whatever). As part of this concept, we must also define the FillRowMethodName property. The function specified in this special property will be implicitly ...