SQL Server Temporary Stored Procedure

Back to: SQL Server Tutorial For Beginners and Professionals

SQL Server Temporary Stored Procedure with Examples

In this article, I am going to discuss the SQL Server Temporary Stored Procedure with Examples. Please read our previous article where we discussed the Return Value in SQL Server Stored Procedure with examples. As part of this article, we are going to discuss the following pointers in detail.

1. What are SQL Server Temporary Stored Procedures?
2. Type of Temporary Procedures in SQL Server.
3. Understanding Private/Local Temporary Stored Procedure?
4. Understanding Public/Global Temporary Procedure in SQL Server?
5. What is the use of SQL Server Temporary Stored Procedure?

What are Temporary Stored Procedures in SQL Server?

The stored procedures which are created temporarily in a database i.e. the stored procedures which are not stored permanently in a database are called temporary stored procedures. The SQL Server Temporary Stored Procedures are of two types such as

1. Private/Local Temporary Stored Procedure
2. Public/Global Temporary Stored Procedure.

What is a Private/Local Temporary Stored Procedure?

When we created the stored procedure by using the # prefix before the stored procedure name then it is called Local or Private Temporary Stored Procedure. The most important point that you need to keep in mind is that the Private/Local stored procedures are executed by the connection which has created it. These are automatically deleted when the connection created is closed. The Syntax for creating a Private or Local Temporary Stored Procedure in SQL Server is given below.

Example: Creating a Local Temporary Stored Procedure in SQL Server.

CREATE PROCEDURE #LocalProcedure
AS
BEGIN
  PRINT 'This is Local Temporary Procedure'
END

-- Calling the Local Temporary Procedure
EXEC #LocalProcedue

This procedure is executed only in the session which has created it. Once the session which created this temporary stored procedure is closed, then this stored procedure is automatically deleted. In SQL Server, we cannot access this local temporary stored procedure from another session.

What are Public/Global Temporary Stored Procedures?

Whenever the stored procedure is created by using the ## prefix then it is called Global Temporary Procedure in SQL Server. The Global temporary stored procedures are accessed by other connections in SQL Server. The most key point that you need to remember is the Global Temporary Stored Procedure can access by any connection until the connection which has created the procedure is not closed.

Once the connection that created the global temporary stored procedure is closed, then no further execution of the Global Temporary Stored Procedure is allowed. Only those connections who have already started executing the Global temporary stored procedure are allowed to complete in SQL Server. The Syntax for creating a Global or Public Temporary Stored Procedure in SQL Server is given below.

Example: Creating a Global SQL Server Temporary Stored Procedure.

CREATE PROCEDURE ##GlobalProcedue
AS
BEGIN
  PRINT 'This is Global Temporary Procedure'
END

-- Calling the Global Temporary Procedure
EXEC ##GlobalProcedue

What is the use of Temporary Stored Procedure?

The Temporary Stored Procedures are useful when you are connecting to the earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches.

In the next article, I am going to discuss how to use the Encryption and Recompile attribute in SQL Server Stored Procedure with examples. Here, in this article, I try to explain the SQL Server Temporary Stored Procedure with Examples. I hope now you understand the need and use of Temporary Stored Procedure.