Given that CREATE PROCEDURE must be the first statement of its batch, what is the use of Temporary Stored Procedures?

Question

I was recently made aware of temporary stored procedures and became shocked that I've never seen them used before in my career. I was very disappointed when I remembered that CREATE PROCEDURE must be the first statement of its batch, which surely limits their usage. This idea was supported when I saw how rarely they get mentioned on this website. They don't even have a tag!

What are the idiomatic use cases for temporary stored procedures? How do they avoid the limitations inflicted by needing to be the first statement in their batches?

Answer 1

common

The most common uses I have for temporary stored procedures are:

1. Skirting permissions and procedural issues
2. Testing parameterized application queries
3. Avoiding ASYNC_NETWORK_IO wait interference: link

What are the idiomatic use cases for temporary stored procedures? How do they avoid the limitations inflicted by needing to be the first statement in their batches?

This is really not what they're good or useful for. You're barking up the wrong tree a bit looking for them.

permissions and procedures

In some environments, as a consultant, I may not have permission to create stored procedures as permanent objects.

This may be because of the level of access my user account has, or because various change management processes disallow ad hoc creation of them.

In those cases, creating a temporary stored procedure that I can execute normally is often faster than editing a stored procedure to turn it into a run-once script.

It's also a useful technique to test changes to existing stored procedures without the fear of accidentally overwriting what's currently in use.

application side

Let's say developers primarily use an ORM of some sort to generate queries, and that they are parameterized. If I want to explore tuning opportunities for one of those, my choices in SSMS are somewhat limited.

Using local variables as substitutes will get you weird query plans in many cases, and using recompile hints with local variables may not be appropriate if the issue is parameter sniffing.

In those cases, I can either:

• Stick the query in parameterized dynamic SQL
• Create a temporary store procedure for the query

Both options are equivalent for testing, but often the temporary stored procedure route is quicker since I don't wouldn't have to worry about messing around with single quotes to double them up.

Answer 2

You can also create global temp stored procedures by prefixing with ## instead of #. In SSMS, you can use the GO terminator to separate batches. For a normal temp stored procedure it's scoped to the session and will live as long as the session is active. For a global temp stored procedure, it can be accessed by any session and lives as long as the session that created it is still open.

I use them for performance tuning. They are particularly useful if you want to try a new version of a stored procedure against a log-shipped copy of your production DB (in standby/read-only mode). I typically script out the stored procedure - prefix the name with ## (Global temp stored procedure) and make whatever alterations I need. I can then take a captured query in another SSMS window and prefix that with ## to test out the new version.

An alternative option is to script out the procedure, remove the CREATE PROC, change the params definition to a DECLARE, and set the parameter values as needed. Creating a temp stored procedure is better - it's easier and you also get parameter sniffing as you would with a regular stored procedure.

Answer 3

I never use and rarely see temporary stored procedures.

I was very disappointed when I remembered that CREATE PROCEDURE must be the first statement of its batch, which surely limits their usage.

Temporary procedures, unlike temporary tables, always have session lifetime. A temp table created in a nested batch (stored procedure or dynamic SQL) is automatically destroyed at the end of the batch. But you can create a temporary proc in a nested batch and use it in a top-level batch, or subsequent nested batch. EG

exec ('create proc #foo as select 1 a')
go
exec #foo
exec ('#foo')