I would venture to say temporary tables in SQL Server are common. What doesn’t seem to be as common is the usage of temporary stored procedures. Let’s take a look at creating temporary stored procedures and a few uses for them.
Everything is Temporary
Creating a temporary stored procedure is similar to creating a temporary table. All you need to do is add a # (hashtag, pound sign, number sign, take your pick) to the front of the object name.
If we were creating a new stored procedure, we could use:
CREATE PROCEDURE ProcTest AS BEGIN SELECT 'Proc in ExampleDB' AS 'Message'; END GO
If we wanted this to be a temp stored procedure, we would use:
CREATE PROCEDURE #ProcTest AS BEGIN SELECT 'Temp Proc in ExampleDB' AS 'Message'; END GO
We can execute our example procedure and see results:
Similar to temp tables, we can also add a second # if we wanted a global temp stored procedure:
CREATE PROCEDURE ##GlobalProcTest AS BEGIN SELECT 'GlobalProc in ExampleDB' AS 'Message'; END GO EXEC ##GlobalProcTest;
Once you close your session, any temporary stored procedures that you had created will be dropped.
Tell Me Why
We know how to create a temporary stored procedure. Why would we ever need to use one?
One use case for temporary stored procedures is testing. It can be safer to create a temp stored procedure for testing changes and then modify the existing “real” stored procedure only when you’re satisfied with such changes.
You may not be able to add a stored procedure into production while working on a task. Still, you want the benefits of calling a stored procedure instead of repeatedly copy/pasting/editing the same script. Whether it’s for security rules or your own safety, using a temporary stored procedure can save you time troubleshooting or researching. No need to check in changes for deploying a new stored procedure to prod.
If you’re tag teaming an issue with others, consider making the temporary stored procedure global and share the love.
Thanks for reading!