Brad over on the
VS Data Team has posted a
walkthrough for deploying a SQLCLR stored procedure.
Honestly, using .Net to create stored procedures to me is just a bad idea, in my opinion. Now you have application developers creating simple stored procedures and want their assembly added to the database, when they may not have the first clue about how performance may be impacted on the server. The developer may have no knowledge of index structures, triggers, foreign keys etc, and could very well be causing a bottleneck with the code they write when its simply doing nothing more than inserting, updating, deleting or selecting data. Leave the database and its purpose to the DBA's. Just because you can create a stored procedure in C# and put it in the database doesn't mean you should. Database programming is more than just creating a stored procedure to update data. If you understand the database architecture and the performance impacts your code will have and can avoid those bottlenecks when implementing your code, then by all means code your heart out, no harm done. I'm going to leave my simple stored procs in the database, written in T-Sql.
Now, to take the other side of the argument. There are some very complex business logic models out there in the world, and sometimes Sql server just can't put that data together cleanly enough, and sometimes, not at all. You end up with a stored procedure or a view that is gigantic, has case statements all throughout, has dozens of subqueries, even more joins and is just a nightmare to have to deal with. In these cases, this is where SQLCLR becomes useful to me. C#/VB is much more capable and just downright easier to code those complex business models when you need some data structures put together, and I am very much looking forward to migrating code already in .Net that handles these situations over to SQLCLR to be executed on the database server where it can become more closely knitted with the database itself.