Create CLR Stored Procedure

With the latest version of SQL Server 2005, we have the the ability to write CLR from any .Net language.

Lets dig into more details:
I have started leaning C#, so let me show you how to write this code in C#. Please bare with me on the logic :)

Step:
1. Start Visual Studio 2005
2. Add new file
3. Select Class with C#
4. Mark the name as DemoProcCls.cs and write the below lines of code.

using System.Data.SqlServer;
using System.Data.SqlTypes;
public class DemoProcCls
{
public static void DemoProc()
{
SqlPipe sqlPipe = SqlContext.GetPipe();
sqlPipe.Send("Hello World");
}
}

i) Compile the code into assembly
You need to find out the SQL Server path of "sqlaccess.dll".

csc /target:library D:\DemoProcCls.cs /r:"sqlaccess.dll"

ii) Load the above created assembly in SQL with the use of CREATE PROCEDURE
create procedure SampleDemoProc
as external name DemoProcCls.DemoProcCls.DemoProc

and execute the stored procedure as

exec SampleDemoProc