What About Temporary Stored Procedures

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:

EXEC #ProcTest;

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!