Cast Stored Procedure Result as a Table? (SQL Server 2008)

Question

Hey all,

I currently have a stored procedure that runs a complex query and returns a data set. I'd like to cast this data set to a table (on which I can perform further queries) if at all possible. I know I can do this using a table-valued UDF but I'd prefer to avoid that at this point. Is there any way I can accomplish this task?

Thanks, Sonny

EDIT: OK... so the SProc I'm using (written by third party and I'm not supposed to change it) runs a fairly complex select statement to return a bunch of line item data about purchase orders. I can recreate it as a UDF but then I'd have to support the UDF and ensure it gets changed as and when our vendor changes their SProc. I'd like to further refine this line item info by a number of criteria such as (but not limited to) item numbers, vendor codes, cost centers, etc. All of this information is brought back by the original SProc and I just need to be able to manipulate it further. My thought process was that if I can somehow treat the results of the SProc as a table (or get them into a table format of some type) then I can run further queries against the original result set to limit by the criteria mentioned above. Please let me know if any further details are needed.

Answer 1

There's various means of sharing data between stored procedures - this link is pretty exhaustive.

But I'm curious why you want a table valued stored procedure (which doesn't exist in SQL Server) when there are table valued functions...

Answer 2

Have you considered using table-valued parameters? They are new in SQL 2008.

-- Edit --

Nope, never mind, they're only good for passing data into stored procedures.

Answer 3

Use the OPENROWSET trick

It's about the best option in this case.

Answer 4

Cast Stored Procedure Result as a Table?

Yes and this is used quite often. It simply needs one or more select statements:

Create Procedure #Foo
As
Select object_id, name
From sys.columns

That said, you cannot join to this resultset nor can you easily consume it from another stored proc (although there is a way). Given your edit, it appears the question is whether you can consume the results of a stored proc by another stored proc. Technically, yes. You can populate a temp table with the results of a proc. However, you must declare your temp variable or temp table with the same column structure as is returned by the first resultset of the stored proc.

Declare @Data Table ( object_id int, name nvarchar(128) )

Insert @Data
Exec #Foo

Select *
From @Data

(Or use the far more clever OPENROWSET solution as mentioned by Cade Roux and OMG Ponies)

Answer 5

You could try using a View instead of a Stored Procedure. Store your complex query as part of the view, and you have the functionality to perform more queries on the view.