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)
Using set deferred_name_resolution makes
this step unnecessary; see “Using deferred name resolution”.
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
This step is unnecessary if you use deferred_name_resolution.
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, which 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 exec inv_amounts
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.
