CODEWITHSUNDEEP
sqlsql-server
Anirudh
Anirudh

Reputation: 21

SQL Server: Load stored procedure results into table

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!

Upvotes: 1

Views: 1101

Answers (2)

Andomar
Andomar

Reputation: 238078

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.

Upvotes: 1

Faiz
Faiz

Reputation: 5453

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...

Upvotes: 0

Related Questions