Temp tables allow developers to create and scan tables within a stored procedure - and
have the tables totally isolated from all other database connections. This is very
valuable when results need to be processed several times within a loop, or when
a complex result set is expected (like a crosstab). Note that temp table transactions
are logged within tempdb (exception: select into create statements).
create proc proc_gen_report (@region_id integer) as
declare @total money
/* standard create */
create table #rpt (
store_id integer not null,
store_cd char(5) not null,
inv_count integer not null,
total_sales money not null
)
/* create using select into - make sure 'select into' is turned on */
select t1.cus_id, t1.cus_name, sum(t2.inv_amount) 'inv_summary'
into #cus_invoices
from customer t1, invoice t2
where t2.cus_id = t1.cus_id
/* Processing occurs, using temp table(s) where needed. */
/* Temp tables can be used in joins, aggregates, updates, etc. */
drop table #rpt
drop table #cus_invoices
return
go
|
|
