MS SQL server: How can I use the result set from a stored procedure in a SELECT statement?

1. Convert the procedure to a function

create function dbo.dataset ()
returns @out table (
 id int,
 name varchar(150)
) as begin
 insert @out(id, name)
 select EmployeeID, FirstName + ' ' + LastName
 from Northwind.dbo.Employees
 return
end
go

select *
from dbo.dataset() 
where name like '%King%'

2. Temporary table

create procedure dbo.dataset_sp 
as begin
 set nocount on
 select EmployeeID, FirstName + ' ' + LastName
 from Northwind.dbo.Employees
end
go

create table #dataset(
 id int,
 name varchar(150)
)
insert #dataset exec dbo.dataset_sp

select *
from #dataset
where name like '%King%'

drop table #dataset

3. Linked Server

EXEC sp_addlinkedserver '(local)'
go

SELECT * FROM bar WHERE
bar_id IN ( SELECT foo_id FROM OPENQUERY([(local)], 'exec
 db_name.owner.sp_GetMatchingIds @Criteria = ''a''' )