SQL Server Temporary Stored Procedure
In this article, I am going to discuss the SQL Server Temporary Stored Procedure with some examples. 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
- Private/Local Temporary Stored Procedure
- Public/Global Temporary Stored Procedure.
What is Private/Local Temporary Stored Procedure in SQL Server?
When we created the stored procedure by using # prefix for the stored procedure name then it is called Local or Private Temporary Stored Procedure in SQL Server. 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: The syntax for creating a local SQL Server Temporary Stored Procedure as shown 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 on 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 is Public/Global Temporary Stored Procedure?
Whenever the stored procedure is created by using ## prefix then it is called as Global Temporary Stored 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.
The syntax for creating a Global Temporary Procedure
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 SQL Server Temporary Stored Procedure?
The SQL Server 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.
The 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.
In this article, I try to explain the SQL Server Temporary Stored Procedure step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.