Temporary Store Procedure in SQL Server

Temporary Store Procedure is feature of SQL Server. But temporary store procedure is little known to us while we are more familiar to temporary Table.

Temporary Store procedure is similar to normal Store Procedure but as name describe it exists temporarily to database.

Like Temporary Table, Temporary Store procedure is also two types:

• Local 
• Global

You can create Temporary Store Procedure as you create normal store procedure but not forget to add # for local Temporary Store Procedure and ## for Global Temporary Store Procedure

Local Temporary Store Procedure available only in the current session( similarity with Local Temporary Table) and is automatically dropped when session end or closed.

Global Temporary Store Procedure is available to all session and is dropped when the session of the user that created it is closed.

You can also read:

• What are joins? How many types of Joins?
• Difference between char and nvarchar / char and varchar data-type?
• How to check if a table exists in SQL Server 2000/2005 using SQL Statement.
• Add a column to an existing table in SQL Server

Example Local Temporary Store Procedure

 CREATE PROC #TempStudentList
 AS
 DECLARE @Student TABLE (Id INT, StudentName VARCHAR(50), Course VARCHAR(50));

 INSERT INTO @Student
VALUES (1,'Rishi Sanuj', 'MCA'),
                         (2, 'Sushant Priyadarshi', 'Nursery'),
                 (3,'Ashish Kumar', 'Engineering'),
                         (4, 'Kundan Kumar','Hotel Management')

SELECT *FROM @Student
RETURN
GO


View Result:

EXEC #TempStudentList

Example Global Temporary Table

CREATE PROC ##TempStudentList
AS
DECLARE @Student TABLE (Id INT, StudentName VARCHAR(50), Course VARCHAR(50));

 INSERT INTO @Student
VALUES (1,'Rishi Sanuj', 'MCA'),
                         (2, 'Sushant Priyadarshi', 'Nursery'),
                         (3,'Ashish Kumar', 'Engineering'),
                         (4, 'Kundan Kumar','Hotel Management')

SELECT *FROM @Student

RETURN

GO

View Result:
        EXEC ##TempStudent

Uses of Temporary Store Procedure

1. When you have not object CREATE permission in any type of database then use it.
2. Test procedure procedure before actually creating it