The "alter table" within the exec statement idea is a good one that I hadn't thought of before 🙂

One place where I've abused this practice is where we have MS Word calling a stored proc for mail merge data. Since the fields can vary slightly I have the proc determine a random global temporary table name of the form ##myTable_ with a newID() appended. That table name is then used in some dynamic SQL to do the work and then an exec statement is used to retrieve data from that table. Without the unique global temp table name you could have collisions with users executing the stored proc simultaneously.

There are good reasons for MS SQL to have the temp tables well scoped and I doubt they're ever going to change 🙂