1. In Visual Studio (I use 2008), choose File -> New Project
2. Choose on Project Type - Database Projects -> Microsoft SQL Server -> SQL CLR
3. Choose your template type. I choose C# SQL Server Project Template
4. Next you will be prompted an ‘Add Database Reference’ with available connections. Choose one or add new reference by clicking the button.
5. Next dialog will ask you ‘Do you wish to enable SQL/CLR debugging on this connection’ - You can cliek Yes (or No if you prefer)

Now, we are going to write a CLR stored procedure.

6. On Solution Explorer, right click on the project and choose Add -> New Item
7. Choose ‘Stored Procedure’
8. Now add some code in place of // Put your code here. My code looks like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure1()
    {
        SqlPipe pipe = SqlContext.Pipe;
        pipe.Send("Hello!!!! this is my first CLR Stored Procedure");
    }
};

9. Build Solution
10. You can see ‘Deploy Solution’ also in the Build menu which will automatically add this new CLR Stored Procedure in your database.

Now CLR Stored Procedure in-action

11. Check on your Microsoft SQL Server Management Studio: Your Database -> Programmability -> Stored Procedures. You must see your new stored procedure listed in the tree. In my case it was dbo.StoredProcedure1.
12. Try executing your stored procedure from a new query page. Eg:

EXEC StoredProcedure1

13. You must see the text. In my case I see “Hello!!!! this is my first CLR Stored Procedure”.

If you get an error “Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.”, then you must enable CLR to execute CLR based stored procedures. You can do this by executing:

exec sp_configure 'clr_enable','1'
RECONFIGURE

Related posts:

1. T-SQL Pass GETDATE() as a parameter for stored procedure
2. Get Windows Edition information from SQL Server
3. Accessing SQL Server Databases with PHP
4. Microsoft SQL Server JDBC Driver 2.0 CTP
5. Troubleshooting Performance Problems in SQL Server 2005