I too like temporary tables, I don't use them to often but find them a good alternative to cursors. Recently I have had reason to use a global temp table which is scary and I don't really like so if anyone has better suggestions let me know.
I have an sp which queries a linked server. The linked server has about twenty databases which have the same table structure along with other misc databases. I need to loop through every database and run a query against it, then return the compiled results.
My current solution involves using a comma delimited string as a sort of array holding the database names, and use a while loop to loop through all the databases. I then use the exec command to create a query with the database variable and load the results into a global temp table. Obviously I am very careful to drop the table at the end but still...
Using a global temp table was the only way I could think of to join and interact with the results of a query run with the exec command. The exec command was the only method I could find that would allow me to write and reuse one query in a stored procedure. Yes it does have to be in an sp.
Something I just thought of is calling another sp from the main sp passing the database variable from one to the other. Then I could compile the results in the main sp. If that works it should get rid of the global temp table but not the exec command, still it might be a step in the right direction.