1

I have a stored Procedure that returns multiple resultsets(to be specific three resultsets). I just need the first resultset. I am calling the original procedure from a different procedure where i will store the returned resultset into a #table and use it from my further processing. Also, i can not modify the original stored procedure to achieve this. Please help!

gbn
  • 422,506
  • 82
  • 585
  • 676
Anirudh
  • 21
  • 1
  • 3
  • possible duplicate of [Facing problem for Insert Stored Procedure Results Into temp table](http://stackoverflow.com/questions/6368669/facing-problem-for-insert-stored-procedure-results-into-temp-table) or http://stackoverflow.com/q/4630359/27535 – gbn Jul 17 '11 at 12:18
  • @gbn: Do those methods really work with multiple result sets? That's what's being asked here. – Jon Seigel Jul 17 '11 at 12:27
  • @Jon Seigel: as far as I can recall, yes. Only the 1st result set will be loaded into the temp table though – gbn Jul 17 '11 at 12:35
  • @Jon Seigel: also, it's the only way unless some client code send the result back... – gbn Jul 17 '11 at 12:40
  • @jon when i create the temp table for first resultset, I get this error "Column name or number of supplied values does not match table definition." I checked my table and values and it is exactly matching with the first resultset so i suspect the following resultsets are causing this error. – Anirudh Jul 17 '11 at 12:40
  • 1
    @gbn - from [INSERT](http://msdn.microsoft.com/en-us/library/ms174335.aspx) - "If execute_statement is used with INSERT, *each result set* must be compatible with the columns in the table or in column_list." (emphasis added) – Damien_The_Unbeliever Jul 17 '11 at 12:46
  • 1
    Okay, I did some testing, and it doesn't work quite like you'd expect. SQL will try to insert the results of *all* the SELECTs into the target table, so either (a) it will blow up if the table definitions aren't compatible, or (b) you get all the results UNION ALL'd together, which I suspect would be undesirable. @gbn – Jon Seigel Jul 17 '11 at 12:48
  • Well, I'm glad we all learned something. :) Unfortunately, it doesn't solve the problem at hand... hmm... to keep it within the database, you'd probably have to resort to using SQLCLR to separate out the result sets, and return only the first. – Jon Seigel Jul 17 '11 at 13:06

2 Answers2

1

It's not possible to retrieve the second or further result set from a stored procedure inside SQL.

Two workarounds:

  • A scheduled job (like a C# program) that periodically calls the stored procedure and stores the result in tables that other procedures can use.
  • A SQL CLR stored procedure that does the same. The advantage of a SQL CLR procedure is that you can call it from normal SQL stored procedures, so you don't have to wait for the scheduled task.
Andomar
  • 232,371
  • 49
  • 380
  • 404
0

I haven't tested this, but a work around would be to use OpenQuery and call your SP using it because "Although the query may return multiple result sets, OPENQUERY returns only the first one". OPENROWSET will also do the same...

Faiz
  • 5,331
  • 10
  • 45
  • 57
  • I can use OpenRowSet but how do i pass the parameters to the openRowSet SELECT * INTO #Temp FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;', 'set fmtonly off exec master.dbo.testproc ,'param1','param2') this does not work :( – Anirudh Jul 17 '11 at 13:21