I just finished writing about 15 stored procedures.
Where I heavily used temporary tables. Why? Because I was given an impossible deadline with incomplete specs that kept changing (here are some Access queries, replicate them in T-Sql and have it done by the end of today (4 hours). So, 40+ MS Access queries got condensed to 15 sp's - but 20+ of the Access queries were "Make Table" queries, so, well, you know). I typically try to avoid temp tables as well. Here's what my fear is: the design of these sp's is atrocious (I am geniunely ashamed that I wrote them) and I fear I will never be given the time to go back, look at the idiocy of the requirements I was given and write clean T-Sql that provides the required output with much less lunacy than was in the original design of the Access queries. My guess is that if I were granted that time, I probably wouldn't have to use any temp tables at all.
That said, I wonder if you or Chris have made much use of "table variables". I haven't really any experience with them. I have a sense of the limitations, but was wondering how you liked them.
bcnu,
Mikem