Temporary Stored Procedure in sql server

Temporary stored procedures on Microsoft SQL Server are prefixed with pound sign #. One pound sign means that it’s temporary within the session, two pound signs ## means it’s a global temporary procedure, which can be called by any connection to the SQL server during its lifetime.

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.

See the example

Local stored procedure

create proc #GetEmpDetails As Begin select EmpID,EmpName,EmpAdd from Test.dbo.Emp End

 

SP created successfully.

We can see this on Temp db

 

Now I am executing this SP

exec #GetEmpDetails

It will be dropped when the session is closed.

Global stored procedure

Global Stored Procedure is start from ##.

create proc ##GetEmpDetails As Begin select EmpID,EmpName,EmpAdd from Test.dbo.Emp End

See the out put

exec ##GetEmpDetails

 It can be used on all session. See the example

 

It is visible to all sessions and is dropped when the session of the user that created it is closed.