Menu
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.
996
Temporary Stored Procedures are similar to normal Stored Procedures, but as their name suggests, have a fleeting existence. There are two kinds of temporary Stored Procedures, local and global.
There is no difference in speed between a query run inside a function and one run inside a procedure. Stored procedures have problems aggregating results, they cannot be composed with other stored procedures.
Creating a temporary procedure Local temporary SQL Server stored procedures: These are created with # as prefix and can be accessed only in the session where it created. This procedure is automatically dropped when the connection is closed. Following is the example of creating a local temporary procedure.
The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.
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.
65
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
