What is the temporary stored procedure?

The stored procedures which are created temporarily in a database (not stored permanently) is called as the temporary stored procedure.

There are two types of temporary stored procedures such as

  1. Private / Local Temporary Stored Procedure
  2. Public / Global Temporary Stored Procedure.
What are Private / Local Temporary Stored Procedure?
  1. These are created with the # prefix added to the procedure name.
  2. Private or Local Temporary Stored Procedures are executed by the connection that created it.
  3. These are automatically deleted when the connection created is closed.

Syntax: The syntax for creating a local temporary procedure

CREATE PROCEDURE #<PROCEDURE NAME>
AS
BEGIN
<PROCEDURE BODY / STATEMENTS / QUERIES>
END

Example: Create a Local Temporary stored procedure.

CREATE PROCEDURE #DelRec
AS
BEGIN
Delete from EMPLOYEE where Eid = 105
END

This procedure is executed on the session which is created it and once the session is closed this procedure is automatically deleted. And we cannot access this procedure once the session is closed also we cannot access this procedure from another session.

What is the Public / Global Temporary Stored Procedure?
  1. These are created with the ## prefix added to the procedure name.
  2. Any connection can execute the global temporary stored procedure.
  3. A Global Temporary Stored Procedure exists until the connection used by the user who created the procedure is closed and any currently executing versions of the procedure by any other connections are implemented.
  4. Once the connection that was used to create the procedure is closed, no further execution of the Global Temporary Stored Procedure is allowed. Only those connections that have already started executing the stored procedure are allowed to complete.

Syntax: The syntax for creating a Global Temporary Procedure

CREATE PROCEDURE ##<PROCEDURE NAME>
AS
BEGIN
<PROCEDURE BODY / STATEMENTS / QUERIES>
END
Example: Create a Local Temporary stored procedure.
CREATE PROCEDURE ##DelRec
AS
BEGIN
Delete from EMPLOYEE where Eid = 105
END
What is the use of a Temporary Stored Procedure?

Temporary Stored Procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches.

Example: Procedure with a default value.

CREATE PROCEDURE PROC3(@X INT= 100, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT'The SUM of the 2 Numbers is: '+CAST(@Z AS VARCHAR)
END

Executing the above procedure:

1. EXEC PROC3 200, 25
2. EXEC PROC3 @X=200, @Y=25
3. EXEC PROC3 @X=DEFAULT, @Y=25
4. EXEC PROC3 @Y=25

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

Ref:
https://dotnettutorials.net/lesson/sql-server-stored-procedure-interview-questions-answers/