Manipulating temporary tables in stored procedures

Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.


Temporary tables with names beginning with “#”

Temporary tables with names beginning with “#” that are created within stored procedures disappear when the procedure exits. A single procedure can:

Since the temporary table must exist in order to create procedures that reference it, here are the steps to follow:

  1. Use create table to create the temporary table.

  2. Create the procedures that access the temporary table, but do not create the procedure that creates the table.

  3. Drop the temporary table.

  4. Create the procedure that creates the table and calls the procedures created in step 2.


Temporary tables with names beginning with tempdb..

You can create temporary tables without the # prefix, using create table tempdb..tablename from inside a stored procedure. These tables do not disappear when the procedure completes, so they can be referenced by independent procedures. Follow the steps above to create these tables.

WARNING! Create temporary tables with the “tempdb..” prefix from inside a stored procedure only if you intend to share the table among users and sessions. Stored procedures that create and drop a temporary table should use the # prefix to avoid inadvertent sharing.