Niels SQL Server Blog

Wow, that was a lot of acronyms in the title :-)! So, this post is about CLR methods used as Table Valued User Defined Functions (TVF). In other words, a User Defined Function that returns a table, instead of a scalar value. You are probably aware that if you want to create a method to act as a TVF, that method has to have a return type of ISqlReader as in the code snippet below:

public static ISqlReader MyTvf() {
  //do some stuff
  //get a ISqlReader instance - ir
  return ir;
} 

What makes this not as straight-forward as it may seem, is the fact that you can not just instantiate, for example, a SqlDataReader (which implements ISqlReader), and fill it with data. Instead you need to have a class that implements ISqlReader, instantiate it, and fill it with data.

When you implement ISqlReader, you also have to supply implementations for ISqlRecord and ISqlGetTypedData, IGetTypedData and IDisposable. This may seem to be quite a few methods to implement, but depending on what you want your reader class to do, you may not have to supply implementation code for all methods. There are however a couple of methods you have to implement:

• ISqlRecord.FieldCount
• ISqlGetTypedData.GetSqlMetaData
• ISqlReader.Read

In addition to the above methods you also need to implement some of the methods from ISqlGetTypedData interface. This is the interface which gives you methods to retrieve the values of the data you return. Methods like:

• GetSqlInt32
• GetSqlChars

So dependent upon what data types your class can handle, you implement the necessary GetSqlXXX methods. As your class holds the data you "serve up" you have to somehow load the data into the class, and supply meta data. This is typically done in the constructor of the class, and below is an example of a constructor for a class that accepts arrays as input:

public InProcReader(Array ar, SqlMetaData[] schema) {
  m_Resultset = ar;
  m_Schema = schema;
}

In the example above the user supplies an array plus an array of SqlMetaData. The constructor parameters are then assigned to local class variables, which are used when reading the data. An example of a CLR method using this particular class and constructor can look like so:

public static ISqlReader TvfTest() {
    
  string[,] sar2 = new string[2, 2];
  SqlMetaData[] smd = new SqlMetaData[2];
  smd[0] = new SqlMetaData("Col1", SqlDbType.NVarChar, 256);
  smd[1] = new SqlMetaData("Col2", SqlDbType.NVarChar, 256);
  sar2[0, 0] = "Test";
  sar2[0, 1] = "of";
  sar2[1, 0] = "Table-Valued";
  sar2[1, 1] = "Functions";
  InProcReader ir = new InProcReader(sar2, smd);
  return ir;
}

In the example above, the array suppled is a string array. Obviously the array data does not have to be of the same data type, you can send in an object array if you want, with different data types for each element.

You can read more about TVF's and UDF's in general in Chapter 3 of our book, and if you install the SQL Server Engine samples you can find the code for a class that implements ISqlReader. That sample is however a bit convoluted, so I have written a somewhat simplified class. You can download the code, basic documentation and test scripts from here.

As always, comments are most welcome!