Writting a CLR Stored Proc

With the launch of SqlServer2005, we got the ability to write CLR from any .Net language.

Overview:

The following code-snippet tells how to write a simple CLR Stored Proc with the use

of C#.

Step:

1. Start Visual Studio2005 or higher
2. Add new file from File -> New -> File
3. Select Class with C#
4. Mark the name as AStepAheadProc.cs
5. Write the following lines:


/* This Example is a part of different
* examples shown in Book:
* C#2008 Beginners: A Step Ahead
* Written by: Gaurav Arora
* Reach at : Gaurav Arora */

using System.Data.SqlServer;
using System.Data.SqlTypes;

public class AStepAheadProc
{
public static void FirstProcProc()
{
//Client output buffer

SqlPipe sqlPipe = SqlContext.GetPipe();

//send the output

sqlPipe.Send("This is demo Store Proc Assembly");

}
}


Explanation :

The sqlContext provides The GetPipe() method returns a SqlPipe that we can use
to send results back to the client.

Steps of Use/Working

1. Compile the above code into assembly


csc /target:library c:\AStepAheadProc.cs /r:"D:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Binn\sqlaccess.dll"


2. Load the created assembly in SQL with the use of CREATE PROCEDURE


create procedure AStepAheadProc
as external name AStepAheadProc.AStepAheadProc.FirstProcProc


3. Execute the Created stored procedure in Query Analyzer


exec AStepAheadProc


Note:

The above code-snippet is just describe how to create a CLT Stored Proc.