Query result set of stored procedure

I would like to make a selection of records returned by a stored procedure.

E.g.

SELECT Name FROM EXEC somestoredprocedure @age = 25

This is wrong, but is there a way to do this?

Maarten

What's somestoredprocedure? UDF?

For instance a procedure returning name and address for persons with a certain age

You can't use stored procedure in select statement.

SELECT tmp.*
FROM OPENROWSET('SQLOLEDB', 'my_sqlserver_name';'my_sqluser_login';'my_sqlpass word',
'EXEC mydatabase.dbo.mystoredprodedure') AS tmp

--HTH--

Thanks mikr0s, that works. Seems a bit strange though that you have to use OLEDB instead of calling the procedure directly.

it can also be done without the OPENROWSET (but still OLEDB is used)

--configure server for data access
EXEC sp_serveroption @@servername, 'data access', true
--get results of a sp like selecting from a table
select * from openquery(my_servername, 'exec master.dbo.mytesting') a

--where my_servername is the name of the sql server, returned by @@servername (not a configured linked server)

--HTH--

Typically the best approach is to place the output into a table/temp table/table valued variable:

create proc testproc1
as begin
select 1
union all
select 2
end
go

declare @procresults table (i int);
insert @procresults exec testproc1;
select * from @procresults;
go