How to extend by using SQL CLR table-valued functions

One of the new features of Microsoft SQL Server 2005 is its integration with the Microsoft. NET Framework Common Language Runtime (CLR). This enables people to incorporate. NET Framework classes and functions into Transact-SQL statements and queries.

There are several mechanisms for CLR integration:

CLR user-defined functions (including table-valued functions).

CLR user-defined type.

CLR stored procedures.

CLR triggers.

This white paper explains how to create a reliable Reporting Services report by using CLR table-valued functions to create report data from a variety of sources, including databases.

Extended Reporting Services

There are several ways to extend Reporting Services and integrate CLR functionality using the. NET Framework, including the following:

• Delivery extensions-passing reports to respond to events

• Render extensions-Displays reports in other formats in addition to Reporting Services support

• Security extensions-Provide your own authentication and authorization mechanism for viewing and managing reports

• Data processing extensions-by developing data that can handle data sources that are not supported by Reporting Services

• Custom report Items-a custom server control that can be embedded in a report to provide functionality other than the built-in controls

This paper explains how to implement table-valued functions to handle data as an alternative to using data processing extensions. For more information about extending Reporting services, see the Reporting Services extension in SQL Server 2005 Books Online.

Using table-valued functions

Table-valued functions are used to programmatically create tables at run time. The tables they create can be used for Transact-SQL query statements like any other database table. When you introduce table-valued functions in SQL Server 2000, you can only create them using Transact-SQL. The following is an example of a table-valued function implemented in Transact-SQL.