Temporary Stored Procedures in Sql Server

Temporary stored procedures are similar to temporary tables. It creates in the tempdb. There are 2 kinds of temp procs: temporary stored procedure and global temporary stored procedure. Temporary stored procedure is only accessible in the session that created it. But we can access global temporary stored procedures from other sessions.

How to Create Temporary Stored Procedure

You can execute temporary stored proc like any other ordinary stored proc. But we must use # symbol before stored proc name.

Example:

create proc #tmpProc
(@dbid int)
as
begin
         select loginame,
DB_NAME(dbid) as dbName 
from sys.sysprocesses where dbid=@dbid
       
end

You must execute in the same session.

exec #tmpProc 5

How to Create Global Temporary Stored Procedure

We must use ## symbol before stored proc name.

create proc ##tmpProcGlobal
(@dbid int)
as
begin
         select loginame,
DB_NAME(dbid) as dbName 
from sys.sysprocesses where dbid=@dbid
       

end

You can execute in the other sessions. But if the main session (created session) closes you cannot access the global temporary stored procedure.

exec ##tmpProcGlobal 5


Why do we use temporary stored procs?

Stored Procedure slowness is a frequent problem for developers. Generally, slow stored procedure is renamed to produce a new stored procedure. And then it is tested. However, the procedure under test is often forgotten. This can cause problems and may return the database to the trash. Database administrator does not like it. To prevent this, a temporary stored procedure can be used. Because when the session closes, the temporary procedure is deleted.