如何通过单次ADO调用获取SQL Server动态SQL记录集

• 你查到的两个说法存在部分认知偏差：
  • SQL Server所有类型的用户定义函数（包括多语句表值函数）都无法执行动态SQL：这个说法完全正确。UDF的运行沙箱限制不允许调用sp_executesql、EXEC()执行动态语句，你贴的UDF代码无法通过编译，根本不能创建成功。
  • 存储过程无法返回表变量：这个说法不准确。存储过程的RETURN关键字确实只能返回整数类型的状态码，不能返回表变量，但存储过程可以直接输出SELECT结果集，ADO原生就支持直接读取这类结果集，完全不需要依赖表变量作为返回载体。
• 你完全不需要使用提到的两种冗余方案，把逻辑改写为存储过程，就能实现单次ADO调用拿到目标记录集。

你原有的动态SQL拼接逻辑几乎不需要大改，只需要把UDF改写为存储过程，调整动态SQL的作用域适配即可：

1. 不需要定义返回的表变量，动态SQL拼接完成后直接执行，其输出的结果集会直接被ADO捕获。
2. 动态SQL中引用的@ReportVariantID通过sp_executesql的参数列表传入，不要直接拼接字符串，避免SQL注入风险，也省去字符串转义的麻烦。
3. 如果业务没有去重需求，把UNION替换为UNION ALL，可以大幅提升查询性能。

改好的存储过程代码如下：

-- =============================================
-- Author:      <snip>
-- Create date: 7/5/2022
-- Description: 传入报表变体ID，返回对应筛选器的展示明细
-- =============================================
CREATE PROCEDURE [dbo].[usp_Reports_DefaultFilterInfoForVariantID]
    @ReportVariantID int
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数的消息干扰ADO读取
    DECLARE @SQL nvarchar(max)
    DECLARE @MainTblSchema varchar(8), @MainTblName varchar(100), @MainTblPKName varchar(50), @DisplayColumn varchar(50)

    SET @SQL = ''
    
    DECLARE Csr CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT tpk.[SchemaName], tpk.[TableName], tpk.[PKName], tpk.[DisplayColumnName]
    FROM    [list].[TablePrimaryKeys] tpk
    INNER JOIN [list].[ReportAndQueryVariantDefaultFilterListInt] df ON tpk.ListIDTypeID = df.ListIDTypeID
    WHERE df.ReportVariantID = @ReportVariantID

    OPEN Csr
    FETCH NEXT FROM Csr INTO @MainTblSchema, @MainTblName, @MainTblPKName, @DisplayColumn

    WHILE @@fetch_status = 0
    BEGIN
        SET @SQL = CONCAT(@SQL, 'SELECT df.ListIDTypeID, df.PKID, tpk.IDTypeDescription, mt.' + QUOTENAME(@DisplayColumn) + '
            FROM [list].[ReportAndQueryVariantDefaultFilterListInt] df
            INNER JOIN [list].[TablePrimaryKeys] tpk ON df.ListIDTypeID = tpk.ListIDTypeID
            INNER JOIN [' + QUOTENAME(@MainTblSchema) + '].[' + QUOTENAME(@MainTblName) + '] mt ON df.PKID = mt.' + QUOTENAME(@MainTblPKName) + '
            WHERE df.ReportVariantID = @ReportVariantID ')

        FETCH NEXT FROM Csr INTO @MainTblSchema, @MainTblName, @MainTblPKName, @DisplayColumn
        IF @@FETCH_STATUS = 0
        BEGIN
            -- 无去重需求请保留UNION ALL，性能远高于UNION
            SET @SQL = CONCAT(@SQL, 'UNION ALL ')
        END
    END

    CLOSE Csr
    DEALLOCATE Csr

    -- 执行拼好的动态SQL，直接输出结果集
    EXEC sp_executesql @SQL, N'@ReportVariantID int', @ReportVariantID = @ReportVariantID
END

和调用普通查询没有区别，单次请求即可拿到Recordset：

• 把ADO Command的CommandType设为adCmdStoredProc
• 新增对应@ReportVariantID的参数，传入目标ID值
• 执行Execute方法直接获取记录集即可，不需要额外的中转步骤。

你之前考虑的两种方案都存在明显缺陷：用永久表承接数据需要处理并发冲突、数据清理问题，多用户同时调用会出现数据串扰；在VBA端拼接SQL需要多一次数据库往返拉取元数据，效率低且维护成本高，完全没有必要采用。

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