I use temporary tables to store large results sets for reporting, usually where the values required cannot be calculated in a single SELECT statement. The reporting runs in a third party application with a scripting language which allows me to create and access the temporary tables. Doing this gave my application a huge performance boost (table hints also helped), and helped solved locking problems I was having due to the applications insistance on starting a transaction before running any script code.
However, I have one process which uses a stored procedure for speed, and have used a shared 'permanent' table because temporary tables go out of scope when the stored procedure finishes, as described in this quote from MS Books Online:
"A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table"
I was wondering if a global temporary table would remain in scope after the procedure had completed, it is not clear from the explanation in Books Online.
Alternatively, I might be able to return a table datatype from the procedure, and create a local temporary table from that to drive the report.
Any suggestions welcome!
If it ain't broke, don't fix it...