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.