Solomon Rutzky (6/27/2013)
Global Temporary Stored Procedures are very helpful when testing updated code in Production, prior to releasing that code. Assuming that it is SELECT only, you can do A / B testing in Production (where ideally we cannot just create objects willy-nilly) to make sure performance and/or logic fixes actually work with the real data set(s). By using Global Temp Procs you can easily open multiple sessions and have one tab for the CREATE / ALTER ##Proc (for easy edits to the code while testing), one tab for running the ##Proc, and another tab for running the current Production code.
By jove, I believe you've found a good use for TSPs, Solomon. Frequently, Development and QA environments don't have the same volume of data that Production has. If you're careful to make sure there are no server-killing accidental many-to-many joins as well as taking other common sense precautions associated with "developing or testing in production", this is a great way to do a little testing and performance checking prior to a release. I'd strongly recommend that Developers make sure the DBA knows so that (s)he can quickly respond if one of the TSPs under test drag the server down but this sounds like the only good use for TSPs. Thanks for the post!
--Jeff Moden
Change is inevitable... Change for the better is not.