I'm sure that CLR will be usefull for situations where TSQL isn't quite up to the job.

Speaking personally, if a developer (with a lot of assistance) can't write something in TSQL and can prove that there's a proper reason for using CLR then i might be open to it, otherwise I just don't want to introduce it onto our systems.

Not everyone is good at writting optimised code (I'm being nice ) and allowing them to create their own stuff is pretty much saying 'here you go, add a bottleneck that we wont be able to find later'.