1

I am trying to get table data which is being returned from Stored procedure.

Create procedure Proc1
as 
begin
Select * from Employee
End
Go

I want to use this as :

Select * from Departments D
inner join (Exec proc1) p
on D.Emp_id = p.Emp_id

Please suggest.

Thanks

Richard
  • 106,783
  • 21
  • 203
  • 265
KMittal
  • 602
  • 1
  • 7
  • 21
  • although you cant have stored procedure directly pull out a table. i am guessing the reason you will want to use a stored procedure is to pass parameters. You can actually use a table-valued function. – MEdwin Jul 26 '18 at 12:03

1 Answers1

1

Short version: you can't. Stored procedures cannot be used as a source of data in a query.

The best you can do is put the results of the stored procedure into a (temporary) table and then query that:

create table #sprocResult (
  -- define columns that match the results of the sproc.
  -- You should also define a PK, and possibly other indexes
)

insert into #sprocResult exec proc1

(You can use a table valued variable as well.)

Richard
  • 106,783
  • 21
  • 203
  • 265