[FIXED] pandas/sqlalchemy/pyodbc: Result object does not return rows from stored proc when UPDATE statement appears before SELECT

Issue

I'm using SQL Server 2014, pandas 0.23.4, sqlalchemy 1.2.11, pyodbc 4.0.24, and Python 3.7.0. I have a very simple stored procedure that performs an UPDATE on a table and then a SELECT on it:

CREATE PROCEDURE my_proc_1
    @v2     INT
AS
BEGIN
    UPDATE my_table_1 
    SET v2 = @v2 
    ;

    SELECT * from my_table_1
    ;
END 
GO

This runs fine in MS SQL Server Management Studio. However, when I try to invoke it via Python using this code:

import pandas as pd
from sqlalchemy import create_engine

if __name__ == "__main__":
    conn_str = 'mssql+pyodbc://@MODEL_TESTING'
    engine = create_engine(conn_str)
    with engine.connect() as conn:
        df = pd.read_sql_query("EXEC my_proc_1 33", conn)
        print(df)

I get the following error:

sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

(Please let me know if you want full stack trace, I will update if so)

When I remove the UPDATE from the stored proc, the code runs and the results are returned. Note also that selecting from a table other than the one being updated does not make a difference, I get the same error. Any help is much appreciated.

Solution

The issue is that the UPDATE statement is returning a row count, which is a scalar value, and the rows returned by the SELECT statement are "stuck" behind the row count where pyodbc cannot "see" them (without additional machinations).

It is considered a best practice to ensure that our stored procedures always start with a SET NOCOUNT ON; statement to suppress the returning of row count values from DML statements (UPDATE, DELETE, etc.) and allow the stored procedure to just return the rows from the SELECT statement.

Answered By - Gord Thompson