Could someone please advise me on the syntax in Transact SQL to execute a stored procedure (that returns records) and then place the results in a temporary table

Thankyou - Alastair

hi alastair,

--Two methods
1) you will have to create a table first and then insert output of stored procedure using
following syntax.

insert into <table> exec <stored_procedure>

create table #t(
spid int null,
ecid int null,
status varchar(500) null,
loginname varchar(500) null,
hostname varchar(500) null,
blk int null,
dbname sysname null,
cmd varchar(800) null)

insert into #t exec sp_who

2) without precreating the table you will have something as follows

EXEC sp_serveroption <server name>, 'data access' , 'true'

select *
into #temptable
from openquery (<server name>, 'exec sp_who')



--
Vishal Parkar
vgparkar (AT) yahoo (DOT) co.in