0

I have a stored procedure that returns 4 result sets. The results sets have lots of columns.

What's the best way to create a table out of each result set? The data types and schema in the tables should be the same as the ones from the result sets.

I know I can do this to create a table from a selection: 

CREATE TABLE TABLE_NAME 
  AS SELECT * FROM USERS

So is there a way to select a result set from a stored procedure execution??

Sava B.
  • 1,007
  • 1
  • 10
  • 21
  • I don't know enough to answer your question (which is why I'm commenting) but this is something I bookmarked a long while back that [might help.](http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure) – AdamMc331 Oct 08 '14 at 17:18
  • 1
    Can you not do a `select into`? if you just get table objects back from the sproc you could probably do a `select * into newtable from @returnedsproctable` – crthompson Oct 08 '14 at 17:22
  • I should have mentioned that I know next to no t-sql. I'm not sure how to select a single result set from a sproc. – Sava B. Oct 08 '14 at 17:23
  • how are they consumed currently? – crthompson Oct 08 '14 at 17:24
  • It's a long story. Short answer is, I'm not sure. They're consumed by a foxpro report in some way. I should mention that the four tables I wish to create from result sets don't yet exist. – Sava B. Oct 08 '14 at 17:25
  • The `into` would create the table. If they existed already you'd need to do an insert. – crthompson Oct 08 '14 at 17:26
  • How do I get @returnedsproctable? – Sava B. Oct 08 '14 at 17:29
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62690/discussion-between-sava-b-and-paqogomez). – Sava B. Oct 08 '14 at 17:31

1 Answers1

1

While this was mostly covered in chat, it should still have an answer for others who might be wondering the same thing.

The only way to access a specific result set out of multiple results sets, and within the context of T-SQL, is via SQLCLR. Using C#, VB.Net, or any .Net language, you would use a SqlDataReader which can access the result sets separately.

The SQLCLR proc would simply exec the existing T-SQL proc and can either spit out a single result set (assuming an input param would specify which one to return as a result set) or could do a separate connection and directly call INSERT statements to do all 4 at the same time (although at that point it could just as well be a Console App or Windows Form or whatever).

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171