Introduction

SQL Server 2005 provides a new feature which allows .NET assemblies to be accessed from User-defined procedures, functions, triggers, new types, and aggregates. CLR functions can be called from T-SQL queries, and CLR procedures can be called from a T-SQL batch as if they were T-SQL procedures. This article illustrates CLR integration for Functions by going step by step through the validation example on MSDN, located here.

Using the Code

The Common Language Rruntime (CLR) integration feature is off by default in Microsoft SQL Server. To enable CLR integration, use the clr enabled option of the sp_configure Stored Procedure.

The SqlFunction attribute indicates the function will be used as a user-defined function, which returns either a scalar value or a table.

The TableDefinition property indicates the function will return a table. The property is the SQL representation of the definition of the returned table.

The method name specified in FillRowMethodName is implicitly called by the SQL/CLR framework each time the MoveNext() method on the returned IEnumerable object (or type that implements the IEnumerable interface) is called. The FillRow method must have a signature that looks like this:

where the first parameter (Object obj) is an object array that contains the values of one output row. The subsequent function parameters (out <col1_type> <col1> etc.) are out parameters that contain the values that will appear in the columns for the row that is being constructed (from http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx).

Step 2: Create the Asssembly in SQL Server

To do this, you use the CREATE ASSEMBLY (Transact-SQL) statement. CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

The local path or network location of the assembly should be replaced with a path to your DLL. SQL Server looks for any dependent assemblies in the same location. The assembly name must be unique within the database.

SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the Registry. Other options are EXTERNAL_ACCES and UNSAFE.

To identify the correct class and method to use from the assembly, EXTERNAL NAME uses:

Assembly Name.ClassName.MethodName

The registered assembly is named CLRSQLExample. The class within the assembly is Validation, and the methods within that class that will be executed are ExtractAreaCode, SampleTableFunction, and RegExMatch.

Pity me I didn't see your post earlier, it took me close to a day to figure out the same. But i have a doubt:

What is the minimum role needed to perform the steps.
Our environment runs with minimum required privileges even in developer environment.
I know with db_owner role for master database it is possible, but I would like to know of any other alternatives.