What are temporary Stored procedures?

The store procedures are stored on a temporary basis. There are 2 types of stored procedure:

Private /Local Temporary Stored Procedure

Public/ Global Temporary Stored Procedure

Local temporary SPs: These kinds of procedures can be executed by the present session only and are dropped at the end of the session. This can be used as the normal procedure and this case be created/executed without access to creating /executing any object. But access for executing statements written inside the procedure should be there.

Syntax:

Create proc #

As begin

-- SQL statements

End

Global temporary SPs: These kinds of procedures can be executed by any session on the server and are dropped when the session who has created the procedure is closed. In case if session which has created the procedure is closed and any of the session is executing the procedure then old sessions are allowed to execute, however any new session starting will not be allowed it will give the error the object doesn't exists

Syntax:

Create proc ##

As begin

-- SQL statements

End