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).
[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[/sql]