Retrieving a SQL Server OUTPUT parameter value from a stored procedure

Question

I have been following the documentation on the pyodbc page on PyPi and I can't seem to get the code to work. https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures

Using Python 3.8.10

conn = pyodbc.connect(connString)
cursor = conn.cursor()
startSQL = '''
    DECLARE @load_id INT;
    EXEC dbo.ETL_StartLoad @JobName = 'test',  @LoadID = @load_id output;
    SELECT @load_id AS Load_ID;
'''
print(startSQL)
cursor.execute(startSQL)
auditList = cursor.fetchall()
print(auditList)

When I run the code it fails with the following error:

auditList = cursor.fetchall()
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

If I copy and paste the SQL in SSMS and run it then it works just fine.

See the updated wiki page. TL;DR - You need to put SET NOCOUNT ON; at the beginning of your anynymous code block. — Gord Thompson
– Gord Thompson, Commented Jan 4, 2023 at 16:05
@GordThompson this worked. Please answer the question so i can give you credit for it. — lem
– lem, Commented Jan 5, 2023 at 21:58

Gord Thompson · Accepted Answer · 2023-01-05 22:09:49Z

1

The most common cause of

pyodbc.ProgrammingError: No results. Previous SQL was not a query.

when executing a stored procedure is that the procedure itself does not include SET NOCOUNT ON;and it performs DML actions (INSERT, UPDATE, DELETE) that return a rowcount. To avoid the error, include SET NOCOUNT ON; at the beginning of the anonymous code block, e.g.,

startSQL = '''\
    SET NOCOUNT ON;
    DECLARE @load_id INT;
    EXEC dbo.ETL_StartLoad @JobName = 'test',  @LoadID = @load_id output;
    SELECT @load_id AS Load_ID;
'''

answered Jan 5, 2023 at 22:09

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lem Over a year ago

The transaction returns the correct value to the command prompt but in the database the transaction is getting rolled back. So the value doesnt exists in the DB anymore when I query it directly. Any Python specific reason for this?

Gord Thompson Over a year ago

Are you calling conn.commit() to commit the changes?

lem Over a year ago

Since the code inside the sproc is wrapped in a transaction I didnt think I needed to commit the sproc code again in the python code. Adding the conn.commit() works. Thank you so much!

Collectives™ on Stack Overflow

Retrieving a SQL Server OUTPUT parameter value from a stored procedure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related