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