使用Python调用SQL存储过程报错：Previous SQL was not a query

已完成字符串doc_id转varbinary的处理，但调用SQL存储过程时出现如下错误：

Error executing stored procedure: No results. Previous SQL was not a query

Python代码 #

with pyodbc.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        # Execute the SELECT query
        try:
            select_query = "SELECT TOP (1) * FROM [MDW].[MDW_F_PurchaseOrder_attaches] where Document_Id = 333631"
            cursor.execute(select_query)
            select_row = cursor.fetchone()

            # Add the SELECT query result to the response message
            response_message += " SELECT Query Result:\n"
            if select_row:
                response_message += f"{select_row}\n"
            else:
                response_message += " No data found in the SELECT query.\n"

        except Exception as e:
            logging.error(f"Error executing SELECT query: {e}")
            response_message += " Failed to execute SELECT query. Check logs for details.\n"

        # Call the stored procedure with parameters
        try:
            stored_procedure = "EXEC MDW.SP_GetDocument @Hash = ?, @Login = ?"
            cursor.execute(stored_procedure, doc_id_binary, user_email)
            rows = cursor.fetchall()

            # Format the results into a readable string
            response_message += " Stored Procedure Result:\n"
            if rows:
                for row in rows:
                    response_message += f"{row}\n"
            else:
                response_message += " No data found for the given parameters.\n"

        except Exception as e:
            logging.error(f"Error executing stored procedure: {e}")
            response_message += " Failed to execute stored procedure. Check logs for details.\n"

SQL存储过程代码 #

ALTER procedure [MDW].[SP_GetDocument] @Hash varbinary(20),@Login varchar(200)
as
set @Hash=isnull(@Hash  ,0x36ACEDC6177EA070E533521B02DF279BCBD4BC93)

select top 1  Source_Db,Document_Id,Displayname
into #docs
 from MDW.MDW_F_PurchaseOrder_attaches
 where Hash_Document=@Hash

 IF @@ROWCOUNT=0
  select Source_Db=null,Document_Id=null,Displayname=null,_Message='Document not found'
 Else
  select *, _message='OK' from #docs where Document_Id %10=1 
  union
  select Source_Db=null,Document_Id=null,Displayname=null,_Message='Not allowed'
  from #docs where Document_Id %10<>1 
GO

报错原因分析 #

存储过程中先执行的select ... into #docs是DDL操作，执行后会返回一个"受影响行数"的结果集（不属于查询结果），之后才是真正返回业务数据的select语句。pyodbc执行存储过程时会先获取这个DDL产生的结果集，此时直接调用fetchall()会因为当前结果集不是查询结果而触发报错。

解决方案 #

方案1：修改Python代码跳过非查询结果集 #

在调用存储过程后，先跳过DDL操作产生的非查询结果集，再获取真正的查询数据：

# Call the stored procedure with parameters
try:
    stored_procedure = "EXEC MDW.SP_GetDocument @Hash = ?, @Login = ?"
    cursor.execute(stored_procedure, doc_id_binary, user_email)
    
    # 跳过所有非查询结果集
    while cursor.nextset():
        pass
    
    rows = cursor.fetchall()

    # Format the results into a readable string
    response_message += " Stored Procedure Result:\n"
    if rows:
        for row in rows:
            response_message += f"{row}\n"
    else:
        response_message += " No data found for the given parameters.\n"

except Exception as e:
    logging.error(f"Error executing stored procedure: {e}")
    response_message += " Failed to execute stored procedure. Check logs for details.\n"

方案2：修改存储过程抑制非查询结果集 #

在存储过程开头添加SET NOCOUNT ON;，直接抑制DDL/DML操作返回的行数结果，从根源避免多结果集问题：

ALTER procedure [MDW].[SP_GetDocument] @Hash varbinary(20),@Login varchar(200)
as
SET NOCOUNT ON; -- 添加此行抑制非查询结果集
set @Hash=isnull(@Hash  ,0x36ACEDC6177EA070E533521B02DF279BCBD4BC93)

select top 1  Source_Db,Document_Id,Displayname
into #docs
 from MDW.MDW_F_PurchaseOrder_attaches
 where Hash_Document=@Hash

 IF @@ROWCOUNT=0
  select Source_Db=null,Document_Id=null,Displayname=null,_Message='Document not found'
 Else
  select *, _message='OK' from #docs where Document_Id %10=1 
  union
  select Source_Db=null,Document_Id=null,Displayname=null,_Message='Not allowed'
  from #docs where Document_Id %10<>1 
GO

添加SET NOCOUNT ON;后，原Python代码无需修改即可正常获取存储过程返回的查询结果。

额外优化建议 #

存储过程中的@Login参数未被使用，可考虑移除该参数或补充对应的权限校验逻辑，避免冗余参数存在。

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