snippetsqlModerate
Given that CREATE PROCEDURE must be the first statement of its batch, what is the use of Temporary Stored Procedures?
Viewed 0 times
storedproceduresthemustcreatestatementwhattemporarybatchprocedure
Problem
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
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?
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?
Solution
common
The most common uses I have for temporary stored procedures are:
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:
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.
The most common uses I have for temporary stored procedures are:
- Skirting permissions and procedural issues
- Testing parameterized application queries
- Avoiding
ASYNC_NETWORK_IOwait 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.
Context
StackExchange Database Administrators Q#334372, answer score: 11
Revisions (0)
No revisions yet.