SQL Server Temporary Stored Procedure
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.
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
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.