SQL Server Temporary Stored Procedure

Temporary Stored Procedure (TSP)

TSP is a temporary stored procedure (database object) created in in TEMPDB database during the current session and is destroyed when the session expires.

Types of Temporary Stored Procedure

There are two types of TSP based on the connection.

1. Private or Local TSP
2. Public or Global TSP

Private or Local TSP is created and visible in a single connection and is destroyed when the connection expires. The local TSP can be executed only within the connection. The local TSP name begins with single pound character.

Public or global TSP is created in database and visible in a multiple connection and is destroyed when the connection which has created it has expired. The global TSP can be executed in any connection. The global TSP name begins with double pound character.

Examples of TSP

CREATE PROC #Localproc
AS
BEGIN
SELECT * FROM Employees
END

Applications of TSP

Temporary stored procedures should be used in specific scenarios where temporary, session-specific logic is required. This can be advantageous in scenarios where you want to perform temporary tasks without cluttering the system with permanent objects.