One thing for sure, we will be revisiting a post such as this one to add more information as we continue to research the subject matter. Our stored procedures have always been transact-sql until about two years ago when we discovered that we could do a lot of things with CLR.
The concept is great and can be used in a lot of scenarios.
Why using one instead of the other?
For simple situations where performance is not an issue and you do not have to deal with too many DML statements, CLR should be your best choice.
For situation when you have to do complex operations (strings and others) CLR is good option.
Other than that you can continue to use Transact SQL.
Anyhow you can always use a combination of both, if you still confused.
Reference
http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx