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.
