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 some examples. Please read our previous article where we discussed the Return Value in SQL Server Stored Procedure with some 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 SQL Server Temporary Stored Procedures?

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 as the temporary stored procedure. 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 Private/Local Temporary Stored Procedure?

When we created the stored procedure by using # prefix for 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 it is closed.

Syntax:

SQL Server Temporary Stored Procedure Syntax

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 ## prefix then it is called as Global Temporary Procedure in SQL Server. The Global temporary stored procedures are accessed by any connection 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 who 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.

Syntax:

SQL Server Temporary Stored Procedure Syntax

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.

Stored Procedure with Default Values:

Let’s see an example of how to use the stored procedure with default values.

CREATE PROCEDURE spAddNumber(@No1 INT= 100, @No2 INT)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = @No1 + @No2
  PRINT 'The SUM of the 2 Numbers is: '+ CAST(@Result AS VARCHAR)
END

-- Executing the above procedure:
1. EXEC spAddNumber 3200, 25
2. EXEC spAddNumber @No1=200, @No2=25
3. EXEC spAddNumber @No1=DEFAULT, @No2=25
4. EXEC spAddNumber @No2=25

In the 3rd and 4th case, it uses the default value of 100 to the variable @No1 which has been given while creating the procedure.

In the next article, I will discuss how to use 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 step by step with some examples. I hope now you understand the need and use of Temporary Stored Procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *