{"id":33,"date":"2013-10-30T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/temporary-stored-procedures"},"modified":"2026-03-18T21:50:20","modified_gmt":"2026-03-18T21:50:20","slug":"temporary-stored-procedures","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/10\/30\/temporary-stored-procedures\/","title":{"rendered":"Temporary Stored Procedures"},"content":{"rendered":"<p>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.<\/p>\n<p>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\u2019t change or simply don\u2019t want to deploy a stored procedure that might only be used once and discarded then temp stored procedures might be the thing for you.<\/p>\n<p>I personally like to use these in situations (<em>such as indicated in the example below<\/em>) where I want to run <a href=\"http:\/\/www.brentozar.com\/blog\/\">Brent Ozar\u2019s<\/a>\u00a0<a href=\"http:\/\/www.brentozar.com\/blitz\/\">sp_Blitz<\/a>\u00a0on an instance that I\u2019m 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 <a href=\"http:\/\/blog.sqlauthority.com\/2011\/11\/26\/sql-server-get-directory-structure-using-extended-stored-procedure-xp_dirtree\/\">xp_dirtree<\/a>\u00a0and have it call <a href=\"http:\/\/sqlblog.com\/blogs\/tibor_karaszi\/default.aspx\">Tibor Karaszi\u2019s<\/a> <a href=\"http:\/\/www.karaszi.com\/SQLServer\/util_restore_all_in_file.asp\">RestoreDatabase_SQL2008<\/a>\u00a0without having to apply the stored procedure permanently\u00a0on the server.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<p>Use the same # or ## prefix that you normally use on tables you can use to create these stored procedures. For detailed syntax information see <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187926(v=sql.110\">MSDN CREATE PROCEDURE (Transact-SQL)<\/a>.aspx).<\/p>\n<pre><code class=\"language-\">IF OBJECT_ID('tempdb..##sp_Blitz') IS NOT NULL\nDROP PROC ##sp_Blitz\nGO<\/code><\/pre>\n<pre><code class=\"language-\">CREATE PROCEDURE ##sp_Blitz\n@CheckUserDatabaseObjects TINYINT = 1 ,\n@CheckProcedureCache TINYINT = 0 ,\n@OutputType VARCHAR(20) = 'TABLE' ,\n@OutputProcedureCache TINYINT = 0 ,\n@CheckProcedureCacheFilter VARCHAR(10) = NULL ,\n@CheckServerInfo TINYINT = 0 ,\n@Version INT = NULL OUTPUT\nAS\nSET NOCOUNT ON;\n--sp_Blitz v16 - December 13, 2012 \n-- (C) 2012, Brent Ozar Unlimited - http:\/\/www.BrentOzar.com\/blitz\n--Rest of code here\nGO<\/code><\/pre>\n<pre><code class=\"language-\">EXEC ##sp_Blitz\n@CheckUserDatabaseObjects = 1 ,\n@CheckProcedureCache = 1 ,\n@OutputType = 'TABLE' ,\n@OutputProcedureCache = 0 ,\n@CheckProcedureCacheFilter = NULL,\n@CheckServerInfo = 0<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-33","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/33","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=33"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/33\/revisions"}],"predecessor-version":[{"id":296,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/33\/revisions\/296"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}