Creating CLR Stored Procedures

By | July 10, 2010

It’s possible to run .NET code inside a Stored Procedure.

How it works:
First create a new “Visual C# SQL CLR Database Project”. (There is a same template for VB.NET. But who uses VB nowadays??? :))
You have to define the connection to your database. If you are using Visual Studio 2010 be sure to choose the .NET 3.5 version. SQL Server <= 2008 doesn't support .NET 4.0 SPs. Afterwards add a new Item called "Stored Procedure". Here a example of a procedure called "SayHello".

public partial class SP
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SayHello(string name)
    {
        SqlPipe p = SqlContext.Pipe;
        p.Send(“Hello ” + name);
    }
};

Now you can deploy the code to your SQL Server. (Right-click on project -> deploy)

You can now call the method from the SQL Management Studio.
CLR Stored Procedure
Have fun! 😉