Temporary Stored Procedures #sproc

CREATE PROCEDURE #MyProcedureName

Yes, you can create temporary stored procedures by prefixing the name of the sproc with a #. The temporary stored procedure is sort of a “leave no trace” stored procedure that is only good for your session. If you use double ## you will get a global temporary stored procedure.

For instance:

CREATE PROCEDURE #thisIsATempProcedure

AS

BEGIN

PRINT 'This is a temp Sproc';

END

GO

 

EXEC #thisIsATempProcedure;

DROP PROCEDURE #thisIsATempProcedure;

I use this quite often for TSQL Scripts that I want to reuse regularly, but where I don’t want to clutter up the clients master or msdb databases with some DBA type stored procedure.

I don’t know what version of SQL Server the Temp Stored Procedure was introduced, but I have just tried it on SQL Server 2000, 2005, 2008, 2008R2, 2012, 2014, and 2016, and it works well on all of those versions.

The temporary stored procedure can only be called from your session, but the global temporary stored procedure can be called from other sessions on your SQL Server.

You can browse for the global temporary sprocs in the Object Explorer unter TempDB, Programmabilty, Stored Procedures.

As far as temporary functions, that’s another story, those are not supported on SQL Server.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!