Is it possible to obtain result set of a sp as a table that I can query?

Question

Hi,

Is it possitble to get a stored-procedure's result set as a table so that I can query that? something like:

SELECT PK_Item, Count(PK_Item) FROM (pMyStoredProcedure) --This sp returns a table that has PK_Item column GROUP BY PK_ITEM ORDER BY PK_ITEM DESC

I am not an T-SQL expert but my friend says it is kind of impossible to do this with sprocs.

Is not there any way? But without modifying the stored procedure.

thanks!

Answer 1

If you know the table structure that the sp will return using sql server 2005

you can use

declare @table table(
  columns here...
)

INSERT INTO  @table exec your_sp params

select * from @table