Thursday, November 23, 2006

Using temporary tables to return usable columns from a stored procedure

use master
go

-- clearup objects
if object_id('master..waittype_summary') is not null 
drop proc waittype_summary
if object_id('tempdb..#waittype_summary_results') is not null 
drop table #waittype_summary_results
go

-- procedure to fetch results from
create proc waittype_summary as
select lastwaittype, 
count(dbid) as process_count,
sum(cpu) as cpu
from master.dbo.sysprocesses
group by lastwaittype
order by sum(cpu) desc;
go

-- temp table to store results
create table #waittype_summary_results
( lastwaittype varchar(30),
process_count int,
cpu int );
go

-- populate temp table
insert into #waittype_summary_results exec waittype_summary

-- display results
select * from #waittype_summary_results

No comments: