tl;dr; Temporary stored procedures are excellent for testing pieces of code that are going to go into (or come from) stored procedures. Because parameters are different than variables.
Have you ever used a temp table? Handy things aren’t they. They allow us to store data into a, well, temporary structure. I’m going to guess 99.5% of you have used them and probably use them frequently. But have you used a temporary stored procedure?
CREATE PROCEDURE #Print1 AS PRINT 1;
This is going to act pretty similarly to a temporary table. If you use a single # it’s a local temporary SP and can only be accessed by this session. Two ##s and it’s a global temporary SP. Interestingly these act very slightly differently. First of all, while global temp tables go away once everyone is done with them (i.e. all locks are gone) and the session that created it is closed, global temp stored procedures go away once the session that created it is closed. No big surprise, there are no locks on stored procedures for them to go away. In fact, even if it’s running in another session it still goes away when the creation session is closed. The run will complete but no other session can start a new run.
Access to global temporary stored procedures is slightly different from global temp tables as well. A global temp table is accessible, as long as it exists, by any other session. A global SP is also accessible by any other session, but only by users that have the connect permission to the database where it was created. Yes, even though a temporary stored procedure exists in tempdb it still seems to have a tie back to the database where it was created. No clue why though.
Ok, now that we’ve talked about how they act (you can check out how security seems to work on them here) why would you want to use them? Well, I was initially introduced to them by Kendra Little (b/t) when she talked about the fact that when tuning stored procedures (particularly pieces of stored procedures) you have to remember that local variables and parameters act differently. Basically, the idea is that if you are tuning a query in a SP, you might pull that query out of the code, set up some variables and run the query. Guess what? Your query plan may not be the same as it was in the stored procedure because parameters are handled differently. I’m not going into a lot of detail here but you can look up parameter sniffing (yes it’s usually a good thing) and forced parameterization to get more information.
In the end, I’ve recently started using these more and more when tuning queries, or if I want to run some test code in a stored procedure but don’t want to leave code behind (I’m terrible about remembering to clean up after myself).
I got kind of excited about these and thought Hey, I wonder what other types of temporary objects I can create?? Yea, turned out I couldn’t find any. You get errors if you try to create temporary views and functions and if you try to create a temporary index you just get an index with a # at the beginning of its name.
Oh well. I’m still excited about temporary stored procedures.