Chapter 16: Using 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, 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.

View this book as PDF