DataGridView无法获取带临时表的存储过程返回数据问题求助

触发原因 #

1. 存储过程未开启SET NOCOUNT ON：你在存储过程中执行了INSERT语句，该语句默认会返回「受影响行数」的信息，ADO.NET的DataAdapter会将这个信息识别为第一个结果集，而你实际需要的查询结果是第二个结果集，和强类型DataTable trace_rtn_dtl 预期的单结果集不匹配，导致填充失败，DataTable为空。
2. 临时表导致的元数据探测失效：ADO.NET的DataAdapter（尤其是强类型DataSet对应的适配器）在预获取存储过程返回结构时，默认会开启SET FMTONLY ON，该模式下SQL Server不会执行实际的存储过程逻辑，只会扫描返回的列结构。但临时表是运行时才创建的，FMTONLY ON模式下不会执行创建临时表的语句，导致SQL Server无法识别最终SELECT返回的列结构，返回的元数据为空，最终填充的DataTable没有数据也没有匹配的列，触发空枚举异常。

解决方法 #

• 首先在存储过程的BEGIN语句后第一行加上SET NOCOUNT ON，关闭DML操作的行数返回：

ALTER PROCEDURE sp_RTN_DTL_Trace
    (@trace_txn_no varchar(12),
     @stock_code varchar(8) = '')
AS
BEGIN
    SET NOCOUNT ON -- 新增该行配置
    -- 保留原有业务逻辑
END

• 如果加了上面的配置还是无效，可以选择以下两种方案二选一：
  1. 替换临时表为表变量：表变量的结构在存储过程定义阶段就会被解析，FMTONLY ON模式下可以正常识别返回列结构，不会出现元数据丢失问题，修改后的存储过程逻辑示例如下：

  ALTER PROCEDURE sp_RTN_DTL_Trace
      (@trace_txn_no varchar(12),
       @stock_code varchar(8) = '')
  AS
  BEGIN
      SET NOCOUNT ON
      -- 声明表变量替代临时表
      DECLARE @report TABLE  
      (
          mixtxnno varchar(12),
          txn_no varchar(20),
          stock_code varchar(8),
          qty int,
          to_loc varchar(5),
          txn_date datetime,
          onhand_qty int,
          txn_loc varchar(12)
      );
  
      INSERT INTO @report(mixtxnno,txn_no, stock_code,qty, to_loc, txn_date, onhand_qty, txn_loc)
      VALUES ('00546599', '000000513761', '81050119', 5, '20022', '2021-09-06', 3, 'Department A')
  
      SELECT * FROM @report
  END
  

  2. 如果你必须使用临时表，可以重新配置强类型DataSet的TableAdapter：打开DataSet设计器，右键adSearch适配器选择「配置」，重新选择对应的存储过程，让设计器重新识别返回的列结构，匹配trace_rtn_dtl的表架构即可。

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