# Miscellaneous > General Database Discussions >  Query result set of stored procedure

## maartens

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

----------


## rmiao

What's somestoredprocedure? UDF?

----------


## maartens

For instance a procedure returning _name_ and _address_ for persons with a certain age

----------


## rmiao

You can't use stored procedure in select statement.

----------


## mikr0s

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

--HTH--

----------


## maartens

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

----------


## mikr0s

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--

----------


## ahains

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

----------

