SQL SERVER :- Temporary Stored Procedures in SQL Server

---

In this post, I am going to discuss about SQL Server Temporary Stored Procedure with examples.

---

Temporary Stored Procedures:-

---

• The stored procedures which are created on temporary basis in a database means the stored procedures which are not stored permanently in a database are called as the temporary stored procedure.
• Temporary Stored Procedures are similar to normal Stored Procedures, but as their name suggests, have a temporary existence.
• There are two types of temporary Stored Procedures, local and global.
• Temporary Stored Procedures are also created just like any other Store procedure but the name must be prefixed with two hash (##) for the global temporary stored procedure and hash (#) for the local temporary stored procedure.
• A local temporary Stored Procedure is only available in the current session and it is dropped when the session is closed or for different session.

---

There are two types of Temporary Stored Procedures in SQL Server such as

• Local Temporary Stored Procedure
• Global Temporary Stored Procedure.

---

Local Temporary Stored Procedure in SQL Server:-

• When a stored procedure created by using the # prefix for the stored procedure name, it is called the local temporary stored procedure in SQL Server
• The most important point that we have to remember is that the Local stored procedures are executed by the connection which has created it.
• Local stored procedures are automatically deleted when the connection is closed.

Syntax:

CREATE PROCEDURE #LocalProcedureName
AS
BEGIN
Procedure Body

END

Example:- Creating Local Temporary Stored Procedure in SQL Server

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


-- Calling the Local Temporary Stored Procedure

EXEC #usp_Temp_SProcLocal

---

• This temporary stored procedure is executed only on the session that has created it.
• Once the session is created for this temporary stored procedure, it is closed, then this stored procedure is automatically deleted.
• In SQL Server, we can not access this local temporary stored procedure from any other session.

 

---

Global Temporary Stored Procedure in SQL Server :-

• Whenever a stored procedure is created using the ## prefix, it is called the global temporary stored procedure in SQL Server.
• Global temporarily stored procedures can be accessed by any connection in SQL Server.
• The most important point that we have to remember is that 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 creates a global temporary stored procedure is closed, then further execution of the global temporary stored process is not allowed.
• Only those connections that have started to perform the global temporary stored procedure are allowed to complete in SQL Server.

---

syntax for creating a Global Temporary Procedure in SQL Server:-

Syntax:-


CREATE PROCEDURE ##LocalProcedureName
AS
BEGIN
Procedure Body

END

Example :- Creating a Global SQL Server Temporary Stored Procedure in SQL Server 

CREATE PROCEDURE ##usp_Temp_SProcLocal
AS
BEGIN
  PRINT 'This is Local Global Temporary Stored Procedure'
END


-- Calling the Local global Temporary Stored Procedure

EXEC ##usp_Temp_SProcLocal

 

---