We are all familiar with table variables, local temp tables, and global temp tables but what some people over look is that stored procedures and functions can also utilize tempdb.
I imagine that the reason for this oversight is because it can be difficult to think of a use for it. To be honest, in almost all cases a permanent stored procedure is the best way to go. But if you are a DBA that often has to look into servers that you can’t change or simply don’t want to deploy a stored procedure that might only be used once and discarded then temp stored procedures might be the thing for you.
I personally like to use these in situations (such as indicated in the example below) where I want to run Brent Ozar’s sp_Blitz on an instance that I’m unfamiliar with or if I wanted to execute a restore of all files in a directory. In that case I could loop through the results of xp_dirtree and have it call Tibor Karaszi’s RestoreDatabase_SQL2008 without having to apply the stored procedure permanently on the server.
Syntax:
Use the same # or ## prefix that you normally use on tables you can use to create these stored procedures. For detailed syntax information see MSDN CREATE PROCEDURE (Transact-SQL).
IF OBJECT_ID('tempdb..##sp_Blitz') IS NOT NULL
DROP PROC ##sp_Blitz
GO
CREATE PROCEDURE ##sp_Blitz
@CheckUserDatabaseObjects TINYINT = 1 ,
@CheckProcedureCache TINYINT = 0 ,
@OutputType VARCHAR(20) = 'TABLE' ,
@OutputProcedureCache TINYINT = 0 ,
@CheckProcedureCacheFilter VARCHAR(10) = NULL ,
@CheckServerInfo TINYINT = 0 ,
@Version INT = NULL OUTPUT
AS
SET NOCOUNT ON;
--sp_Blitz v16 - December 13, 2012
-- (C) 2012, Brent Ozar Unlimited - http://www.BrentOzar.com/blitz
--Rest of code here
GO
EXEC ##sp_Blitz
@CheckUserDatabaseObjects = 1 ,
@CheckProcedureCache = 1 ,
@OutputType = 'TABLE' ,
@OutputProcedureCache = 0 ,
@CheckProcedureCacheFilter = NULL,
@CheckServerInfo = 0
[…] Temporary Stored Procedures […]