How to capture the result set from a stored procedure?

[dee]

Hello,
When I ran the following code, it got the message "No results. Previous SQL was not a query." How do I get the dataset returned from the stored procedure? Thank you.

conn_str = ('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';Trusted_Connection=yes;')
cnxn = pyodbc.connect(conn_str)

cursor = cnxn.cursor()
sp = "EXEC spTest"
dataset = cursor.execute(sp)
df_Test = dataset.fetchall()
print(df_Test)

deanhystad write May-24-2024, 04:19 PM:
No BBcode Answer
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button

[deanhystad]

Read the docs. Always good advice.

https://learn.microsoft.com/en-us/sql/re...rver-ver16

In this case the info is right near the top.

DECLARE @return_value int
 
EXEC    @return_value = [dbo].[GetCustomerEmail]
        @CustID = 10
 
SELECT  'Return Value' = @return_value

You need to declare a variable. Call the procedure, telling the procedure to put results in the variable. Use the variable to retrieve the results.

[dee]

Thanks deanhystad,

Those are SQL code. How do I get the dataset in Python? Your example is returning a single value in SQL or Python? It seems in SQL.

I tried the link you provided for the Examples of returning data using a result set. I added RETURN at the end of the stored procedure, but I got the same message "No results. Previous SQL was not a query."

In Python, I just use dateset = the SQL return value (or dataset)?

Thanks again.
Dee

[deanhystad]

More read the docs? This is pyodbc specific.

https://github.com/mkleehammer/pyodbc/wi...Procedures

[dee]

Thanks again deanhystad,

That works. It returned the dataset. Now it needs to be converted to a data frame like a table row and column layout. Do you have more reading for me?

I tried pd.DataFrame(rows) but all columns combined in one long column.

[deanhystad]

Read the string like it is a CSV file. Read about pandas.read_csv() and io.StringIO(str).