If you are wondering what .NET stored procs are going to look like, check this article out.
|re: SQL Server "Yukon" and the CLR
I'm beyond excited about Yukon ever since they released some info on it. I have some many use cases it will fulfill now without having to write complex XPs or odd batch processing programs ... Though I'm weary on the whole object data types I think there are some place where this could be used effectively, for instance a DateRange data type would be totally helpful ... also being able to create your own aggragates is a huge benefit ... these are just some of the minor things that i see ... its too far off ... it makes projects now seem painful knowing what you'll one day be able to do ...