-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|