In one of my recent projects I learnt how to use CLR stored procedures. So what exactly are CLR stored procedures? Well they allow you to run code (C#) inside SQL Server 2005. So instead of creating stored procedures or UDF (User Defined Functions) with SQL code you use C# code to achieve this. There are various scenarios where you would want to do this, and here are a few reasons why:

• Complex code – C# (or any CLR language) has much better support for doing complex string manipulations or hectic calculations than SQL Server does. SQL server does not have support for generics and collections, for example, and to be honest Lists and Dictionaries are very convenient structures
• Functionality that extends beyond the scope of SQL Server 2005. I had to, for instance, check a POP3 email account and update DB values based on the results from checking the POP 3 account. The POP 3 functionality does not exist, and cannot be replicated with plain SQL Code.

There are however a few instances where I think you should not use CLR stored procedures:

• When the assemblies you are adding are not tested and supported. What I mean by this is that for example System.Drawing is considered ‘unsafe’, and you have to wonder why MS do not make it easy to integrate this. Personally I think you should stick to things that are supported, and tested.
• If you’re CLR sproc does data access (selects, updates, inserts) or simple queries. SQL Server 2005 is a very powerful piece of software

I also found that by assigning my CLR sprocs to Jobs I could leverage off the power that SQL Server 2005 provides, and let my sprocs run at intervals without having to write a windows service.