Temporary function or stored procedure in T-SQL

Question

Is there any chance to create temporary stored procedure or function on MS SQL 2005? I would like to use this stored procedure only in my query so after execution it will be gone.

I have a query I would like to EXEC against some data. But for every table I will process this command, I need to change some parts of it. So I thought I would create temporary SP that would return for me a query from arguments I provide (like table name and so on) and than execute this query by EXEC.

And this stored procedure will be not useful for me later so I would like to have it temporary so that when I end executing my query - it will disappear.

Answer 1

This question is a bit old, but the other answers failed to provide the syntax for creating temporary procedures. The syntax is the same as for temporary tables: #name for local temporary objects, ##name for global temporary objects.

CREATE PROCEDURE #uspMyTempProcedure AS BEGIN   print 'This is a temporary procedure' END 

This is described in the "Procedure Name" section of the official documentation. http://technet.microsoft.com/en-us/library/ms187926%28v=sql.90%29.aspx

I'm using this technique to deduplicate the code for my primitive T-SQL unit tests. A real unit testing framework would be better, but this is better than nothing and "garbage collects" after itself.