Table Valued CLR Function in SQL Server

Introduction

In SQL Server 2005 and later versions, database objects such as functions, Stored Procedures, etc., can be created, already created in CLR. With the help of CLR functions, we can create table value functions and Scalar Value functions.

How to create a CLR Table Value Function?

In some cases, CLR is faster than T-SQL. CLR is mainly used to do tasks that are impossible with T-SQL. Follow the steps below to create a CLR table value function in SQL Server.

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

The following example uses the following code 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 depends 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 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. There is no requirement that the entire set of results is materialized in a single table. CLR table-valued functions represent a streaming alternative.