0

Is it possible to return multiple datasets from a stored procedure?

e.g.

CREATE PROCEDURE [dbo].[usp_vvvv] (....)
.....
    SELECT *
    FROM #_temp_client_details

    SELECT *
    FROM #_temp_address_details

    DROP TABLE #_temp_client_details
    DROP TABLE #_temp_address_details
    ....
END TRY
BEGIN CATCH
    ..
END CATCH
END

In my scenario, I need to get datasets from 3 different temp tables into my python code.

All 3 different temp tables are constructed using same tables, same parameters with some manipulations. So, I don't want to create 3 separate stored procedures (because all 3 stored procedures are going to use 90% same SQL logic).

I currently get the output from one of the stored procedure like: 

 import pandas as pd
  def execute():
    query='exec [zzz].[dbo].[sp_zzz]?, ?, ?, ?'
            df = pd.read_sql(query, cnxn, params=[astart, aend, lstart,
                                            lend])

            writer = pd.ExcelWriter('output.xlsx')
            df.to_excel(writer, index=False, sheet_name='client') 
            writer.save()

Is it possible? If so how can I get all tables output into pandas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ratha
  • 9,434
  • 17
  • 85
  • 163

1 Answers1

1

Yes, this should work. Check out the use of the nextset() function in the Python documentation here: https://www.python.org/dev/peps/pep-0249/

Dale K
  • 25,246
  • 15
  • 42
  • 71
Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • Im using pandas to read data from sql stored procedure. Is there an option in panadas read_sql ? – Ratha Dec 03 '19 at 01:51
  • Please ensure your answer is stand-alone i.e. doesn't depend on links which could break in the future. – Dale K Dec 03 '19 at 01:56
  • Hi Ratha, not one that I'm aware of. As an aside, have you just tried reading again from the same connection? – Greg Low Dec 03 '19 at 03:43
  • Hi Dale, isn't a reference to the nextset() function in the Python documentation sufficient by itself? (i.e.with or without the link) – Greg Low Dec 03 '19 at 03:44
  • @GregLow as it stands its suitable for a comment, but not sufficient for an answer. An answer would have some code demonstrating *how* that function solves the OPs problem. – Dale K Dec 03 '19 at 04:05
  • e.g. https://stackoverflow.com/questions/7263781/pyodbc-return-multiple-cursors-from-stored-procedure-with-db2 – Dale K Dec 03 '19 at 04:08