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:
Since the temporary table must exist to create procedures that reference it, here are the steps to follow:
  1. 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.
  2. 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
  3. Drop the temporary table:

    drop table #tempstores

    This step is unnecessary if you use deferred_name_resolution.

  4. 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.

Related concepts
Deferred Name Resolution Usage