Table Valued CLR Function in SQL Server 2005

Introduction

In SQL Server 2005 and later versions of it, database objects such as functions, Stored Procedures, etc. can be created which are already created in CLR. In some of the cases CLR is faster than T-SQL. CLR is mainly used to do those tasks which are not possible with T-SQL. With the help of CLR functions we can create table value functions as well as Scalar Value Function s.

How to create a CLR Table Value Function

Step 1: Create Code in C# or VB.NET

In the following example, the following code is used to create a function that returns a List of custom entities.

Note: For the table value CLR function you must define "FillRowMethodName" and "TableDefinition".

In FillRowMethodName is implicitly called by the SQL/CLR framework each time the MoveNext() method on the returned IEnumerable object is called.

The FillRow method must have a signature:

private static void MethodName(Object obj, out <col1_type> <col1>, out <col2_type> <col2>, ... )

Here col1_type is SQL Data Type like SqlInt32, SqlString, etc.

The number of out columns is dependent on the number of properties in the custom entity, as in:

[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRowCustomTable", TableDefinition = "ID int,Name nvarchar(255)")]

public static IEnumerable GetCustomers()

{

    List<myEntity> entity = new List<myEntity>();

    entity.Add(new myEntity { ID = 1000, Name = "Jignesh Trivedi" });

    entity.Add(new myEntity { ID = 1001, Name = "Tejas Trivedi" });

    entity.Add(new myEntity { ID = 1002, Name = "Rakesh Trivedi" });

    entity.Add(new myEntity { ID = 1003, Name = "S G Trivedi" });

    return entity;

}

public static void FillRowCustomTable(object resultObj, out SqlInt32 ID, out SqlString Name)

{

    myEntity myResult = (myEntity) resultObj;

 

    ID = myResult.ID;

    Name = myResult.Name;

}

public class myEntity

{

    public int ID { get; set; }

    public string Name { get; set; }

}

Step 2: Generate assembly (compiled as a .DLL) for the above code and put it in a shared folder or put it on the SQL Server machine.

Step 3: Create assembly and also create a function (the step is the same as the Scalar Value Function step 3 and step 4)

 

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'CLRFunctions' and is_user_defined = 1)

DROP ASSEMBLY [CLRFunctions]

GO

CREATE ASSEMBLY CLRFunctions FROM '\\DESJT\JT\CLRFunctions.dll';

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomer]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[GetCustomer]

GO

CREATE FUNCTION [dbo].[GetCustomer]()

RETURNS  TABLE (

      [ID] [int] NULL,

      [Name] [nvarchar](255) NULL

) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [CLRFunctions].[CLRFunctions].[GetCustomers]

GO



Conclusion

Table Valued Function with CLR integration is very powerful. CLR table-valued functions represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table.

Reference

http://msdn.microsoft.com/en-us/library/ms131103.aspx