Sql server temporary stored procedures

We can create temp or temporary stored procedure just like normal user defined procedure except procedure name will start with #. For example:

CREATE PROCEDURE #uspProcName(

     @ntParameter AS INT

)

AS

BEGIN

     SELECT @ntParameter AS vcData

    

END

Temporary procedures are created in tempdb database. We can check it by following sql query:

SELECT * FROM tempdb.sys.procedures

You may find name something like: #uspProcName___________________________________________________________________________________________________Sql server adds some postfix in the temp objects in sql server to distinguish the objects of same name from different sessions._________0000040D

Executing temporary stored procedures in sql server:

We can execute the temp stored procedures just like normal stored procedure. For example:

EXECUTE #uspProcName 10

Since temp procedures are created in tempdb so if we will specify the schema name or database name while referencing it, it will ignore it. For example:

EXECUTE matser.db.#uspProcName 10

Or

EXECUTE [This db doesn't exist].[Any schema].#uspProcName 10

 

Both of the execute statement will execute the tempdb.dbo.#uspProcName

Scope of temp stored procedure in sql server:

It is scope is within session where it has created.  If we will try to execute temporary stored procedure from different session we will get the error message. For example, open the new query window in SSMS to create a new session and try to execute this stored procedure:

EXECUTE #uspProcName 10

Or

EXECUTE tempdb.dbo.#uspProcName 10

We will get error message: Could not find stored procedure '#uspProcName'.

How to remove or delete temporary store procedure in sql server:

A. By using DROP statement

DROP PROCEDURE #uspProcName

B. By killing the session. Close the query window.

Interesting observation in temp stored procedures:

1.  Since temp stored procedures are created in tempdb so it is executed from any database in same session. For example:

USE master

GO

CREATE PROC #uspGetDb

AS

     SELECT DB_NAME()

    

EXECUTE #uspGetDb

--Output: master

GO

USE msdb

GO

EXECUTE #uspGetDb

--Output: msdb

--or

EXECUTE master.dbo.#uspGetDb

--Output: msdb

2. Temp stored procedure search the objects in the database where it has created first time not in the database where is being executes. For example:

USE master

Go

--Creating table in master database.

CREATE TABLE tblMaster(ID INT IDENTITY)

INSERT tblMaster DEFAULT VALUES

GO

--Creating proc in master database but it has created in tempdb

CREATE PROC #uspShowData

AS

     SELECT * FROM tblMaster

     SELECT DB_NAME()

    

GO

USE msdb

GO

--Executing this proc in msdb database

EXECUTE #uspShowData

Output:

1

msdb

Global temporary stored procedure in sql server:

If name of stored procedure has prefix ## such procedure are global temporary stored procedures. Its scope is in all connections and it is also created in the tempdb. For example:

CREATE PROCEDURE ##uspGlobalTempProc(

     @ntParameter AS INT

)

AS

BEGIN

     SELECT @ntParameter AS vcData

    

END

Note: When we restart the sql server all objects like tables, procedures etc. are deleted from tempdb database either it is temporary or normal objects since tempdb is re-created every time SQL Server is started.

SQL server interview questions and answers with explanation
Writing dynamic sql queries in sql server
Difference between temporary table and table variable in sql server
Difference between varchar and nvarchar in sql server.
Difference between char and varchar in sql server.
Variables in sql server 2008 R2