CLR Table-Valued Functions (TVFs)

Download latest development release with samples from http://www.effiproz.com/downloads/effiproz_sl4_1_1_beta.zip

EffiProz 1.1 supports CLR Table-Valued Functions  in a way that is very similar to how TVFs are supported by SQL Server. Following example shows how to create a TVF in effiproz database.

First write the CLR function,

public class IdNameEntry

{
public int Id { get; set; }

public string Name { get; set; }

public IdNameEntry(int id, string name)
{

Id = id;

Name = name;
}
}
  
public class TVFFunctions 
{

[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod()
{

List list = new List();
list.Add(new IdNameEntry(0, "car"));
list.Add(new IdNameEntry(1, "van"));
list.Add(new IdNameEntry(2, "bus"));

return list;
}

public static void FillRow(Object obj, out int? id, out string name)
{

IdNameEntry idNameEntry = (IdNameEntry)obj;

id = idNameEntry.Id;

name = idNameEntry.Name;

}

}

Now we create the TVF  and execute as below,

CREATE FUNCTION testTVF()
RETURNS TABLE (id INT, name VARCHAR(100))
EXTERNAL NAME ' EffiProz.Test.SelfTest.TVFFunctions.InitMethod';

SELECT id,name FROM testTVF();