Solomon Rutzky (6/27/2013)

---

cschlieve (6/25/2013)

---

How would you use them. I cannot think of a case where this would be useful?

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.

Take care,

Solomon...

Edit:

I forgot to mention that in order to get a true apples-to-apples comparison on new vs current code, you also need to make a copy of the current code as a Global Temporary Stored Procedure. You now have two Global Temp Procs to EXEC and the execution of the "current" code temp proc will neither impact, nor be impacted by, the actual current code proc (i.e. cached query plan).