CREATE TEMPORARY STORED PROCEDURES, V2K

SQL Server allows you to create temporary stored procedures in the same
way you create temporary tables. Temporary stored procedures are useful
if you do not wish to create a regular or permanent procedure in your
database application.

A temporary stored procedure uses the tempdb database; therefore, you
can create the procedure and then essentially forget about it. Because 
the
tempdb database is re-created each time SQL Server is started, it is
unnecessary to clean up or drop the procedure. This is useful if you 
have a
set of personalized tools or procs that you use frequently but which 
you
do not wish to maintain within the database application.

Just as temporary tables have scope, so do temp procs. Using a single
pound (#) or a double-pound sign (##), you can create your procedure as
either local or global scope. The following simple procedure delivers a 
list
of databases on your server.

CREATE PROCEDURE #GetDBList
AS
SELECT NAME FROM master.dbo.sysdatabases ORDER BY NAME
GO

EXEC #GetDBList
GO

In the EXEC statement above, the scope is local to the current
connection and the procedure is unavailable to subsequent connections.

CREATE PROCEDURE ##GetDBList2
AS
SELECT NAME FROM master.dbo.sysdatabases ORDER BY NAME
GO

EXEC ##GetDBList2
GO

In the preceding EXEC statement, the scope is global to the current
connection and the procedure is available to subsequent connections.

Use temporary procedures when you have procedures that you do not want
to maintain. Keep in mind, however, that each time you recycle the SQL
Server, you will have to re-create the temporary procedures. Placing 
the
procs in one large script file is an easy solution to this problem.

----------------------------------------