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 “#” that are created within stored procedures disappear when the procedure exits. A single procedure can:
Create a temporary table
Insert data into the table
Run queries on the table
Call other procedures that reference the table
Since the temporary table must exist in order to create procedures that reference it, here are the steps to follow:
Use create table to create the temporary table.
Create the procedures that access the temporary table, but do not create the procedure that creates the table.
Drop the temporary table.
Create the procedure that creates the table and calls the procedures created in step 2.
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.
