Temporary function or stored procedure in T-SQL

Hello 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 dissapear.

From stackoverflow

• Just use the SQL of the stored proc inside your query. No need to create a stored procedure inside the DB, it won't give you any advantage over a normal query inside your query.

• Re your edit - it sounds like you should be using sp_ExecuteSQL against a (parameterized) nvarchar that contains TSQL.

  Search on sp_ExecuteSQL; a simple example:

  DECLARE @SQL nvarchar(4000),
   @Table varchar(20) = 'ORDERS',
   @IDColumn varchar(20) = 'OrderID',
   @ID int = 10248
  
   SET @SQL = 'SELECT * FROM [' + @Table + '] WHERE ['
    + @IDColumn + '] = @Key'
  
    EXEC sp_executesql @SQL, N'@Key int', @ID
  

  Note that table and column names must be concatenated into the query, but values (such as @Key) can be parameterized.

  ---

  There is a temporary stored procedure - but it is per connection, not per sp.

  However, you might want to look at Common Table Expressions - they may be what you are after (although you can only read from them once).

  Maybe if you can clarify what you are trying to do?

  gbn : Can you clarify the "temporary stored procedure" bit? Do you mean sp_prepare or sp_executesql?

  tomaszs : I've added some more expl about what I try to acomplish. Hope it helps to understand my idea.

  marc_s : +1 for mentioning CTE ! A vastly under-used feature in SQL Server 2005...

  tomaszs : Thank you, that makes sense!