Hi All,

Is there is any way to select the rows returned by executing a stored procedure. For example I have written a stored procedure like this

create proc ProcPublishers
as
use pubs
select * from publishers
go

and
tried to execute an statement like this to obtain the rows returned by the stored procedure.

SELECT * FROM EXEC ProcPublishers

But it is returning error. Is there is any other way to select the rows returned by procedure.

Please any one help me.

__________________
Regards,
dsekar_nat

Just

EXEC ProcPublishers.

Or

INSERT INTO #Temp
EXEC ProcPublishers

Hi Peso,

Thanks for replying. But still have doubt in this.

What can I do if I am using this in a view

For example

CREATE view publishers_view as
   exec ProcPublishers
    union
   some other select query

while creating this view, error gets returned.

just as peso said , u need not to use select in sprocs , you do that with function
just type the name like -
sp_helpdb
or
exec sp_helpdb

for using temp table , u need to create it first ..

You cannot use a stored procedure as a derived table, more's the pity. Use select into as shown by Peso and use the temp table from then on.

--

Joe (Microsoft MVP - XML)