Temporary function or stored procedure in T-SQL

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.