Temporary function or stored procedure in T-SQL

Question

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.

Answer 1

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.

Answer 2

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?