Creating a SQL Server CLR Procedure

I ventured into unchartered territory (for myself) and created a SQL Server CLR Procedure today. It is actually very easy.

First, create a class library project in VS 2005/2008. Create a class with a static method that will be the CLR Procedure

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace SQLCLRTest
{
public class SQLCLRDemo
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
   using (SqlConnection conn =
        new SqlConnection("context connection = true"))
   {
       conn.Open();
       SqlCommand cmd = new SqlCommand();
       cmd.Connection = conn;
       cmd.CommandText = "SELECT 'Hello World!' AS Greeting";
       SqlContext.Pipe.ExecuteAndSend(cmd);
   }
}
}
}

Then you have to register the assembly in SQL Server

CREATE ASSEMBLY SQLCLRTest FROM 'C:\work\sandbox\SQLCLRTest\SQLCLRTest\bin\Debug\SQLCLRTest.dll'

Then you create the procedure

CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME SQLCLRTest.[SQLCLRTest.SQLCLRDemo].HelloWorld

You might have to enable CLR execution in SQL2005

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

Then you can appreciate the fruits of your labour

exec HelloWorld

Greeting
------------
Hello World!

(1 row(s) affected)

Now that was easy!

MSDN reference here

Error when creating CLR procedure here

Enabling CLR in SQL Server here