Temporary Stored Procedures

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]