Temporary Stored Procedures

Temporary stored procedures are related to stored procedures as temporary tables are related to tables. You use them when you expect to reuse the execution plan of a stored procedure within a limited time frame. Although you can achieve the same functionality with a standard user-defined stored procedure, temporary stored procedures are a better solution because you do not have to worry about maintenance issues (such as dropping the stored procedure).

Temporary stored procedures reside in the tempdb database and must be named with the prefix #. You create them in the same way you create user-defined stored procedures. The only change is the use of a # as a name prefix. This prefix signals the server to create the procedure as a temporary stored procedure. This kind of stored procedure can only be used from the session in which it was created. When the session is closed, it will be dropped automatically. This behavior indicates why this type of stored procedure is often also referred to as a private temporary stored procedure.

The following code example creates a private temporary stored procedure:

      Create Procedure #dbo.ap_Eq!d_List           @Make varchar(50),           @Model varchar(50)      as           Select Eqld           from dbo.Equipment           where Make = @Make           and Model =  @Model      return 

Note 

Sometimes, all user-defined stored procedures in tempdb are referred to as temporary stored procedures. This is incorrect because tbere are major differences between tbe two. For example, user-defined stored procedures stored in tbe tempdb database are accessible to all autborized users and are not limited to tbe session in wbicb tbey were created. These stored procedures stay in tempdb until tbe server is sbut down, at wbicb time tbe complete content of tempdb is flusbed.