Transact-SQL User's Guide
|Chapter 14 Using Stored Procedures|
|Creating and executing stored procedures|
|Using temporary tables in stored procedures|
You can create and use temporary tables in a stored procedure, but the temporary table exists only for the duration of the stored procedure that creates it. When the procedure completes, Adaptive Server automatically drops the temporary table. A single procedure can:
Create a temporary table
Insert, update, or delete data
Run queries on the temporary table
Call other procedures that reference the temporary table
Since the temporary table must exist to create procedures that reference it, here are the steps to follow:
Create the temporary table using a create table statement or a select into statement. For example:
create table #tempstores (stor_id char(4), amount money)
Create the procedures that access the temporary table (but not the one that creates it).
create procedure inv_amounts as select stor_id, "Total Due" = sum(amount) from #tempstores group by stor_id
Drop the temporary table:
drop table #tempstores
Create the procedure that creates the table and calls the procedures created in step 2:
create procedure inv_proc as create table #tempstores (stor_id char(4), amount money)
When you run the inv_proc procedure, it creates the table, but it only exists during the procedure's execution. Try inserting values into the #tempstores table or running the inv_amounts procedure:
insert #tempstores select stor_id, sum(qty*(100-discount)/100*price) from salesdetail, titles where salesdetail.title_id = titles.title_id group by stor_id, salesdetail.title_id
You cannot, because the #tempstores table no longer exists.
You can also 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 above steps to create these tables.