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.
Create a temporary table
Insert, update, or delete data
Run queries on the temporary table
Call other procedures that reference the temporary table
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 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.