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).