Retrieving a SQL Server OUTPUT parameter value from a stored procedure-CodePudding

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.

CodePudding user response：

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;
'''