They can be handy sometimes if you want to modularize your code for the life-time of a routine. To create a temp stored procedure, all you do is use the # sign before the name of the sproc. Same concept as a temp table. Only the owner can execute it and its life is that of the session. Here’s an example that uses the Northwind db.
CREATE PROCEDURE #GetCustomers ( @companyName VARCHAR(50) ) AS BEGIN SELECT * FROM Customers WHERE CompanyName LIKE '%' + @companyName + '%' END
To use it:
EXEC #GetCustomers 'The'
You can find the temp sproc in the tempdb:
If you want more information on the temp sproc, you can query the system view under the tempdb:
SELECT * FROM tempdb.sys.objects WHERE [object_id] = OBJECT_ID('tempdb.dbo.#GetCustomers')
Your next question maybe if you can create temp user defined functions? The answer is a big juicy delicious, “NO!” (at least as of SQL Server 2008 Enterprise).