Temporary Stored Procedures | Inside Microsoft SQL Server 2000

Temporary stored procedures allow an execution plan to be cached, but the object's metadata and the text of the procedure are stored in the system tables of the tempdb database—in sysobjects and syscomments. Recall that tempdb is re-created every time SQL Server is restarted, so these objects no longer exist after SQL Server is shut down. During a given SQL Server session, you can reuse the procedure without permanently storing it.

Typically, you use a temporary stored procedure when you want to regularly execute the same task several times in a session, although you might use different parameter values and you don't want to permanently store the task. You could conceivably use a permanent stored procedure and drop it when you're finished, but you'd inevitably run into cleanup issues if a stored procedure were still hanging around and the client application terminated without dropping the procedure. Because temporary stored procedures are deleted automatically when SQL Server is shut down (and tempdb is created anew at startup), cleanup isn't an issue.

Just as SQL Server has three types of temporary tables, it also has three types of temporary stored procedures: private, global, and those created from direct use of tempdb.

Private Temporary Stored Procedures

By adding a single pound sign (#) to the beginning of the stored procedure name (as in CREATE PROC #get_author AS…), you can create the procedure from within any database as a private temporary stored procedure. Only the connection that created the procedure can execute it, and you can't grant privileges on it to another connection. The procedure exists for the life of the creating connection only; that connection can explicitly use DROP PROCEDURE on it to clean up sooner. Because the scoping of a private temporary procedure is specific only to the connection that created it, you won't encounter a name collision if you choose a procedure name that's used by another connection. As with temporary tables, you use your private version, and what occurs in other connections is irrelevant.

Global Temporary Stored Procedures

By adding two pound signs (##) to the beginning of the stored procedure name (as in CREATE PROC ##get_author AS…), you can create the procedure from within any database as a global temporary stored procedure. Any connection can subsequently execute that procedure without EXECUTE permission being specifically granted. Unlike with private temporary stored procedures, only one copy of a global temporary stored procedure exists for all connections. If another connection has created a procedure with the same name, the two names will collide and the create procedure statement will fail. Permission to execute global temporary procedures defaults to public and can't be changed. You can issue a command to deny other users permission, but it will have no effect. Any user, on any connection, can execute a global temporary stored procedure.

A global temporary stored procedure exists until the creating connection terminates and all current execution of the procedure completes. Once the creating connection terminates, however; no further execution is allowed. Only the connections that have already started executing are allowed to finish.

Procedures Created from Direct Use of tempdb

Realizing that tempdb is re-created every time SQL Server is started, you can create a procedure in tempdb that fully qualifies objects in other databases. Procedures created in tempdb in this way can exist even after the creating connection is terminated, and the creator can specifically grant and deny execute permissions to specific users. To do this, the creator of the procedure must have create procedure privileges in tempdb. You can set up privileges in tempdb in one of two ways: you can set privileges in model (the template database) so that they will be copied to tempdb when it is created at system restart, or you can set up an autostart procedure to set the tempdb privileges every time SQL Server is started.

Here's an example of creating a procedure in tempdb and then executing it from the pubs database:

 USE tempdb GO CREATE PROC testit AS SELECT * FROM pubs.dbo.authors GO -- Executing the procedure created above from the pubs database USE pubs EXEC tempdb..testit

While we're on the subject of temporary objects, keep in mind that a private temporary table created within a stored procedure isn't visible to the connection after the creating procedure completes. You can, however, create a local temporary table before executing a stored procedure and make the table visible to the stored procedure. The scoping of the temporary table extends to the current statement block and all subordinate levels.

NOTE
You can use the @@nestlevel system function to check for the visibility of temporary tables. A temporary table created at nest level 0 will be visible to all further levels on that connection. A table created within a procedure at nest level 1, for example, won't be visible when execution returns to the calling block at nest level 0. A global temporary table, or a table directly created in tempdb without using either # or ##, will be visible no matter what the nesting level.