Temporary Stored Procedures in SQL Server: Local and Global

Temporary stored procedures are procedures that live only as long as you need them. A name that starts with one # sign makes a local one, and two # signs make a global one. Both are stored in tempdb, and both vanish without any cleanup.

Why Use One

I create a temporary procedure whenever I rewrite a slow procedure during a health check. The old version stays untouched in the database. The new version exists only in my session, so I can test it, compare it, and leave nothing behind. If I forget to clean up, SQL Server does it when the session ends.

A regular copy named ProcedureNew has a cost. Somebody must remember to drop it, and an application can start calling it. A temporary copy can’t leak that way.

Local Procedures

The demo creates a database named TempProcDemo with a two-row orders table. A local procedure follows, and the CREATE statement must be the first one in its batch. If another statement comes first, SQL Server answers with Msg 111.

IF DB_ID(N'TempProcDemo') IS NULL CREATE DATABASE TempProcDemo;
GO
USE TempProcDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (OrderID int PRIMARY KEY, Item nvarchar(30) NOT NULL);
INSERT INTO dbo.Orders (OrderID, Item) VALUES (1, N'Notebook'), (2, N'Pencil');
GO
CREATE PROCEDURE #ShowOrders @minId int
AS
SELECT OrderID, Item FROM dbo.Orders WHERE OrderID >= @minId;
GO
EXEC #ShowOrders 1;

Temporary stored procedures with one # sign belong to the session that created them. A local procedure works like any other procedure inside its session, and it accepts parameters. SQL Server stores it in tempdb under a long name. Several sessions can each create a #ShowOrders. Every copy gets its own suffix, and the name is padded to 128 characters.

SELECT LEFT(name, 11) AS NamePrefix, LEN(name) AS NameLength FROM tempdb.sys.procedures WHERE name LIKE N'#ShowOrders%';

Now open a second query window and try to run the same procedure there. It fails, because a local procedure belongs to the session that created it.

EXEC #ShowOrders 1;

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#ShowOrders'.

Global Procedures

A global procedure uses two # signs, and every session can call it. Create it in the first window.

CREATE PROCEDURE ##DoubleIt @n int
AS
SELECT @n * 2 AS Doubled;
GO
EXEC ##DoubleIt 21;

The same call works from the second window, with the same result. A global procedure is useful when a test needs several sessions to call the same code. It also carries a risk. Assume that any login can call it, so keep nothing sensitive in it.

A global procedure lasts until the session that created it ends and no other session is using it. When the creator leaves and no one is running it, the next call from another session fails with Msg 2812. Treat the name as a loan, and give it a name that nobody else would pick.

Which Database Does It Use?

This question causes the most confusion. A temporary procedure lives in tempdb, but it doesn’t run against tempdb. The names inside it resolve in the database where you created it. DB_NAME() reports the database that is current when you call it. The two can differ.

The next script creates a global procedure while connected to TempProcDemo. It counts the Orders table through sys.objects, and it reports DB_NAME(). Then it calls the procedure from master.

CREATE PROCEDURE ##WhereAmI
AS
SELECT DB_NAME() AS CurrentDatabase, (SELECT COUNT(*) FROM sys.objects WHERE name = N'Orders') AS OrdersTablesFound;
GO
USE master;
GO
EXEC ##WhereAmI;

The procedure still finds the Orders table of TempProcDemo while master is current. A global procedure returns data for the database where it was created, even if you run a USE statement first. To work across many databases, write the procedure with dynamic SQL, or create it again in each database.

Both kinds appear in the tempdb catalog. A busy server with many sessions that each create temporary procedures adds many entries there. Drop yours when you finish testing, instead of waiting for the session to end.

Is It Worth It?

You could argue that a regular procedure is safer. If your session drops, a temporary procedure and all your work in it disappear. A lost connection can cost an hour of edits. That’s a real risk.

My answer is to keep the real script in a file. The temporary procedure is the test copy. When the test passes, you deploy the file with CREATE OR ALTER, and the temporary copy has done its job. To compare the old and new versions fairly, run both in one window with SET STATISTICS IO and TIME on. Compare logical reads and CPU time, not only the clock.

What to Remember

Temporary stored procedures are test copies that clean up after themselves. One # means local, and two mean global. Create the procedure first in its batch. Call it from the same session. Remember that its names resolve in the database where you created it. Remove the demo when you finish.

DROP PROCEDURE ##DoubleIt;
DROP PROCEDURE ##WhereAmI;
DROP PROCEDURE #ShowOrders;
GO
USE master;
GO
ALTER DATABASE TempProcDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TempProcDemo;

A temporary procedure is not a draft to lose, it is a test copy that cleans up after itself.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.