Since SQL 2005 it has been possible to write stored procedure in .NET. This is part of the CLR hosting of SQL Server.
When SQL 2005 was released, I was quite skeptical about the two features enabled by CLR hosting, ie CLR Stored Procedure and CLR User Defined Type (UDT). I must say I was quite impressed at the effort Microsoft went through to enable this. Hosting the CLR in a way that was both safe and scalable required a lot of changes from the CLR 1.0 to CLR 2.0 (changes ASP.NET benefited from as well). I always wonder why they went to so much effort to enable something that looks like a gadget to me.
To this day I still think that using CLR UDT should really be the last option. I’ve never seen it deployed anywhere either. For me, SQL isn’t an object-oriented / procedural language and getting CLR types in SQL feels pretty awkward and inefficient. The killer for me would be to maintain CLR data stored in SQL table. What happens to your existing data if you update a CLR type? Data in SQL is meant to be lasting. CLR types belong to the OOP world: agile and changing.
That being said, I finally got around a scenario where I would need to use CLR in a stored procedure. We required to have enterprise web services be accessible from SQL. Everywhere I’ve looked on the web, the best practice approach was to use CLR Stored Procedure. There was some old OLE objects but every expert discourages you to use it. So I went for it and started a proof of concept.
I was happily surprised. The development experience is quite good: you develop in Visual Studio, it deploys your assembly by itself, you can even debug. One of my requirements was to impersonate the calling user which was pretty trivial to do in .NET.
Where I saw more problems was some feature changes in SQL 2008. I was using VS 2010 with SQL 2008 R2. When I wanted to stream outputs out of a SQL sproc, the implementation has changed and I got many security exceptions I couldn’t get rid of. So at the end, I ended up doing only a web service call in CLR and all the rest in SQL. Conservative approach which I recommend!