使用pyodbc获取SQL Server存储过程OUTPUT参数值时遇执行错误求助

问题场景 #

使用Python 3.8.10 + pyodbc调用SQL Server存储过程，代码如下：

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)

运行时抛出错误：

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

但将这段SQL复制到SSMS中运行完全正常。

原因 #

pyodbc执行包含EXEC的SQL批处理时，会先返回存储过程的执行状态结果集（无查询数据），此时直接调用fetchall()会因为当前结果集不是查询结果而触发报错。

解决方法 #

方法1：跳过状态结果集，获取查询结果 #

执行SQL后，先调用cursor.nextset()跳过存储过程返回的第一个状态结果集，再获取后续的查询结果：

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)
# 跳过存储过程的执行状态结果集
cursor.nextset()
auditList = cursor.fetchall()
print(auditList)

方法2：直接绑定输出参数（更推荐） #

不需要在SQL中手动声明变量和查询，利用pyodbc的输出参数绑定功能，直接获取返回值：

conn = pyodbc.connect(connString)
cursor = conn.cursor()
# 简洁写法
load_id = cursor.execute("EXEC dbo.ETL_StartLoad @JobName = ?, @LoadID = ? OUTPUT;", 'test', pyodbc.OutputParameter()).fetchval()
print(load_id)

# 另一种参数声明写法
load_id_param = pyodbc.Parameter()
load_id_param.outputSize = pyodbc.SQL_INTEGER
cursor.execute("EXEC dbo.ETL_StartLoad @JobName = ?, @LoadID = ? OUTPUT;", 'test', load_id_param)
print(load_id_param.value)

内容的提问来源于stack exchange，提问作者lem