Temporary Stored Procedures

You can also set stored procedures to be temporary, which means that once a client disconnects the stored procedure is dropped. Temporary stored procedures are stored in the tempdb database. However, heavy use of temporary stored procedures is not a recommended practice on SQL Server 2005 databases. The support exists for backward compatibility with version 6.5 databases. The older architecture had a more primitive prepare-compile-execute architecture.