MCTS 70-433 SQL Server creating CLR stored procedures written using the .NET framework

To create a CLR stored procedure you must include the "EXTERNAL NAME" clause of the CREATE PROCEDURE statement.

You are in effect defining a .NET static class method. 

After compile the class, you must then register the assembly in SQL Server using CREATE ASSEMBLY.

The following article gives a good over of how to work CLR stored procedures, I have cut-out a key piece here, the full article is more detailed: 

By default the SQL Server .NET Data Provider is added as a reference, along with an include statement for its System.Data.SqlServer namespace. Plus, you can see the System.Data reference, which provides support for ADO.NET and its data-oriented objects such as the DataSet and the System.Data.SqlTypes namespace that provides support for the SQL Server data types.

It's up to you to fill in the rest of the code that makes the stored procedure work. The following example illustrates the source code required to create a simple CLR stored procedure that imports the contents of a file into a varchar or text column:

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.IO

Partial Public Class StoredProcedures

     _

    Public Shared Sub usp_ImportFile _

    (ByVal sInputFile As String, ByRef sColumn As String) 

           Dim sContents As String

           Try

                 Dim stmReader As New StreamReader(sInputFile)

                 sContents = stmReader.ReadToEnd()

                 stmReader.Close()

                 sColumn = sContents

           Catch ex As Exception

                 Dim sp As SqlPipe = SqlContext.Pipe()

                 sp.Send(ex.Message)

            End Try

       End Sub

End Class

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1251402,00.html