Temporary Tables in Stored Procedures

You can create and use temporary tables in a stored procedure, but they exists only for the duration of the stored procedure that creates it. When the procedure completes, SAP ASE automatically drops temporary tables.

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)
```
Note: Using set deferred_name_resolution makes this step unnecessary.

Create the procedures that accesses the temporary table.

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)       
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

When you run the inv_proc procedure, it creates the #tempstores table, inserts a row into it, and executes subprocedure inv_amounts. The #tempstores table only exists until inv_proc exits.

Trying to insert values into the #tempstores table or running the inv_amounts procedure after executing inv_proc will fail:

execute inv_proc       
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  
execute inv_proc       
exec inv_amounts

Errors are raised because the #tempstores table no longer exists after inv_proc has completed.

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.