About the new SQLCLR TVFs

In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple TVF (Bernoulli) implemented using ISqlReader. It contains over 400 lines of code. Many of the methods are stubbed-out because they are never used, but must exist to satify the interface definition. Using the new implementation this method would be less than 15 lines of code.

The new TVF implementation requires three pieces:
1. The SqlFunction attribute with the new field FillRowMethodName.
2. This attribute is applied to a method that returns either IEnumerable or IEnumerator.
3. FillRowMethodName points to a DIFFERENT method (in the same class) that has a special signature.

The methods in steps #2 and #3 have to be public static. Many of the collection classes in the BCL (e.g System.Array) implement IEnumerable or IEnumeration already, or you can write your own implementation.

The FillRowMethodName method has the following signature:

public static void FillIt(Object o, out int col1 , out int col2…)
   where the first arg is object returned by method in step #2
   where the varargs arguments (col1, col2….) are the columns that will be returned.

MoveNext is called on the underlying IEnumerator (in each case) until it returns false. Each time MoveNext returns a value, the FillRowMethod is called. This generates the rows. The number of columns is determined by the exact signature of the FillRowMethod. In this example, a 2-column table is returned.

Interestingly, the 2-nth arguments in your FillRowMethohd must be declared as "out" variables in C#. In my cursory testing, if they are declared as "ref", the method failed with the error: "argument n cannot be NULL" when the TVF implementation calls your FillRowMethod. This is interesting for VB.NET programmers because there is no direct variable qualifier keyword that corresponds to C#'s out. Or is there?

When .NET was first released a friend of mine, Jose Mojica, published "The C# & VB.NET Conversion Pocket Reference". And it names the following VB.NET equivalent for "out":

Imports System.Runtime.InteropServices
' signature of a FillRowMethod
Shared Sub FillIt(o as Object, <Out()> ByRef col1 as Integer, <Out()> ByRef col2 as Integer…)

Works great, Jose, my VB.NET TVF is working fine. If you're doing cross language work in .NET, I highly recommend this book.