14

I'm using python\pyodbc and would like to access the second result set of a stored procedure. As near as I can tell, pyodbc does not support multiple result sets. Additionally, I can't modify the stored procedure. Are there any options to access the second result set using SQL or some other work-around? Perhaps create a second stored procedure that only returns the second result set of the first?

2 Answers2

20

No need for anything fancy. Just use the cursor's nextset() method:


import pyodbc

db = pyodbc.connect ("")
q = db.cursor ()
q.execute ("""
SELECT TOP 5 * FROM INFORMATION_SCHEMA.TABLES
SELECT TOP 10 * FROM INFORMATION_SCHEMA.COLUMNS
""")
tables = q.fetchall ()
q.nextset ()
columns = q.fetchall ()

assert len (tables) == 5
assert len (columns) == 10
Nate Anderson
  • 18,334
  • 18
  • 100
  • 135
TJG
  • 2,011
  • 1
  • 14
  • 5
0

There are a few possible methods here. If the result sets are all the same, you might be able to use the INSERT...EXEC method. Otherwise OPENQUERY might work.

Tom H
  • 46,766
  • 14
  • 87
  • 128