http://www.sqlservercentral.com/articles/Temporary+Stored+Procedures./99822/

Temporary Stored Procedures

By Harsha Majety, 2013/06/25

Temporary stored procedure is a little known feature of SQL Server.  This article is an attempt to throw some light on this interesting feature.
Temporary stored procedures are like normal stored procedures but, as their name suggests, have fleeting existence. There are two kinds of temporary stored procedures local and global. Temporary stored procedures are created just like any other SPs; however the name must begin with a hash (#) for a local temporary SP and two hashes (##) for a global temporary stored procedure.
A local temporary stored procedure is available only in the current session and is dropped when the session is closed. A global temporary stored procedure is visible to all sessions and is dropped when the session of the user that created it is closed. If there are any executing versions of the global stored procedure when the creator session is closed, those are allowed to complete, but once they are done and the creator session is closed, no further execution of the global temporary stored procedure is allowed.
Aside from local and global temporary stored procedures, you can also create regular stored procs in tempdb. These only exist until sql server is restarted because tempdb is recreated each time server is restarted.
The following exercise illustrates these points. First, open a new query window and run the following script and keep the query window open.

-- Local Temporary Stored Procedure
CREATE PROC #HarshaTest
AS
DECLARE @Table TABLE ( col1 INT );

INSERT INTO @Table
 ( col1 )
VALUES ( 1),(2),(3),(4);

SELECT * FROM @Table

RETURN 0
GO

-- Global Temporary Stored Procedure
CREATE PROC ##HarshaTest
AS
DECLARE @Table TABLE ( col1 INT );

INSERT INTO @Table
 ( col1 )
 VALUES ( 1),(2),(3),(4);

SELECT * FROM @Table

RETURN 0

GO
USE tempdb
GO
-- Stored procedure in tempdb
CREATE PROC dbo.HarshaTest
AS
DECLARE @Table TABLE ( col1 INT );

INSERT INTO @Table
 ( col1 )
VALUES ( 1),(2),(3),(4);

SELECT * FROM @Table

RETURN 0

GO

 You should see this in Management Studio:

Zoom in  |  Open in new window

The picture shows stored procedures in tempdb while the session is active. Notice the long postfix that SQL Server automatically adds to the local temporary stored procedure. SQL Server adds some postfix to local temporary objects to distinquish the objects with the same name from different sessions.
Now, in the same window, execute the local & global temporary stored procedures that you created . Both will work and return results.

EXECUTE #HarshaTest;

EXECUTE ##HarshaTest;

The results should look like those shown below:

 

Keeping this window open, open a new query window and execute the procs again. You will notice that local temp stored proc will fail with ‘object not found' error, but global temporary stored proc will work, as shown below.

In the messages tab, you should see:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#HarshaTest'.
Now close the window in which you ran the create proc scripts and execute the procs in another window. You will notice that both will fail with object not found error since the session is now closed.

Checking Management Studio 

The picture shows the procedures in tempdb after the session is closed. Note that local and global procs that were created don’t exist anymore.
An interesting observation about temp stored procedures is, since they are created in tempdb, they can be called from any database. SQL Server basically ignores database name and schema name in the case of temporary stored procedures.
For example, the following two commands work and return the same results . Note that [blah] is a non-existing db.

EXECUTE ##HarshaTest;

EXECUTE [blah].dbo.##HarshaTest;

If you are wondering if it is possible to create temporary functions or temporary views , unfortunately SQL Server does not support Temporary Functions and Temporary Views .
Example : The following SQL will throw an error 

CREATE FUNCTION #HarshaTempFunction()
RETURNS VARCHAR(100)

AS
BEGIN

RETURN('This is a Temporary Function');

END

The results are:
Msg 1074, Level 15, State 1, Line 1
Creation of temporary functions is not allowed.

Limitations:

• Heavy use of temporary stored procedures can create contentionon system tables in tempdb . You also use sp_executesql instead of temporary procs. sp_executesql does not save information to system tables and hence there is no contention.

• You cannot create CLR stored procedures/ functions as temporary objects.

Uses of Temporary Stored procedures:

One use for temporary stored procedures is code reuse and improved error handling when you don’t have object CREATE permissions in any of the databases to create stored procedures.
Example : In SSIS package SQL scripts, if the user doesn’t have object create permission but wants to take advantage of code reuse and execution plan caching features of stored procedures, he can create temporary stored procedures.
You can also use temporary procedures to test a stored procedure before actually creating it. Instead of creating and dropping a permanent stored proc, you can create a temporary stored proc and make it permanent after it performs satisfactorily .
During deployment, if you have a script that uses repetitive code, you can create a temporary stored proc at the beginning of the script and reuse it throughout the script. The temporary stored proc is dropped when the connection is closed so you will not have code lying around after deployment.
For example:

CREATE PROC #tempDeploymentRowsInserted
@stepId INT,
@rowCount INT
AS
BEGIN 
INSERT INTO RowsInsertedDuringDeploymentSteps(StepID,RowsCount) VALUES (@stepId, @rowCount);

END

GO
DECLARE @rowCount INT;

--{Code}

SET @rowCount = @@ROWCOUNT;
EXEC #tempDeploymentRowsInserted 1,@rowCount;

--{Code}

SET @rowCount = @@ROWCOUNT;
EXEC #tempDeploymentRowsInserted 2,@rowCount;

--{Code}

SET @rowCount = @@ROWCOUNT;
EXEC #tempDeploymentRowsInserted 3,@rowCount;

--Code

Temporary stored procedures are useful when connecting to earler versions of SQL server that do not support reuse of execution plans for T-SQL statement or batches( < SQL server 7.0 ) . You can take advantage of execution plans using temporary stored procedures. ODBC Drivers for instance make use of temporary stored procedures.
One use of global temporary stored procedures is code reuse during coordinated collaborative work. When a team is working on something (maintenance work, etc) and there is a code segment that everyone uses during the duration of the work, it can be put in a global temporary stored procedure. After the work is done, the user that created the global temporary variable can close the session and there will not be any vestigial code on the server.
In summary, temporary stored procedures are analogous to temporary tables. They can be use to store code that can be reused throughout the life of your connection.