CLR Functions

You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the .NET Framework common language runtime (CLR). 

Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

1. Create the .Net function

Define the function as a static method of a class in a language supported by the .NET Framework.

2. Compile CLR Code

In order to use this code, the code has to be compiled first to create a DLL.

Run from the CMD:

For vb file: 

vbc /target:library C:\Tmp\ClrExample.vb

For C# file: 

Csc /target:library C:\Tmp\ClrExample.Cs

3. Enabling CLR Integration:

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

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

4. Create assembly in SQL Server

CREATE ASSEMBLY [ClrExampleAssembly] FROM 'C:\Tmp\ClrExample.dll'

GO

5. Create a Function or Stored-Procedure using the CLR function

CREATE FUNCTION [dbo].[ClrExampleUserDefinedFunc](<parameters of the function>)

RETURNS <TYPE>

WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME ClrExampleAssembly.<Class name in the code>.<Function name in the code>

<TYPE> examples:

TABLE ([StringCol] [nvarchar](max) NULL) 

RETURNS [nvarchar](255)

CREATE  PROCEDURE dbo.ClrExampleSP

<parameters of the function>

With Execute As Caller 

As 

External Name ClrExampleAssembly.<Class name in the code>.<Function name in the code>

Go

6. Use it!

-- FUNCTION:

SELECT * FROM dbo.ClrExampleUserDefinedFunc(<parameters of the function>)

--SP:

Execute ClrExampleSP <parameters of the function>

http://www.mssqltips.com/tip.asp?tip=1665

http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx