从动态 SQL 获取 ADO 记录集

小编典典

从动态 SQL 获取 ADO 记录集

sql

我需要能够在 SQL Server 中运行动态查询，并将结果存储在 ADO 记录集中（在 VBA 应用程序中）。我读过不可能在函数（甚至是多语句函数）中运行动态 SQL - 这是正确的吗？我还读到不可能从存储过程中返回表变量 - 这也正确吗？如果是这样，我该怎么做？

我能想到的唯一方法包括：

有一个永久表，使用存储过程插入其中，然后运行一个函数来获取结果，或者
查询我需要在 ADO 中创建动态 SQL 的信息，使用 VBA 生成 SQL 字符串，然后单独查询。

这些都不是理想的。我真正想要的是：

-- =============================================
-- Author:      <snip>
-- Create date: 7/5/2022
-- Description: This function is for displaying details about report/query variant filters.
--              It takes the Report Variant ID, and returns all the display details from [list].[ReportAndQueryVariantDefaultFilterListInt]
-- =============================================
CREATE FUNCTION [dbo].[udf_Reports_DefaultFilterInfoForVariantID]
(
    -- Add the parameters for the function here
    @ReportVariantID int
)
RETURNS 
@Tbl TABLE 
(
    -- Add the column definitions for the TABLE variable here
    ListIDTypeID int
    , PKID int
    , [IDTypeDescription] varchar(50)
    , ValueDisplay varchar(200)
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @SQL nvarchar(max)
    DECLARE @MainTblSchema varchar(8), @MainTblName varchar(100), @MainTblPKName varchar(50), @DisplayColumn varchar(50)

    SET @SQL = 'INSERT INTO @Tbl (ListIDTypeID, PKID, IDTypeDescription, ValueDisplay) '

    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
        -- Quotename is needed if you ever use special characters
        -- in table/column names. Spaces, reserved words etc.
        -- Other changes add apostrophes at right places.
        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
            SET @SQL = CONCAT(@SQL, 'UNION ')
        END
    END

    EXEC sp_executeSQL @SQL

    CLOSE Csr
    DEALLOCATE Csr

    RETURN 
END

有什么方法可以完成此操作并通过单个 ADO 调用返回记录集？

阅读 140

2022-07-22

共1个答案

小编典典

您不能在函数中执行动态 SQL。但是你可以使用存储过程。

还有其他问题：

您在前后都有额外的括号QUOTENAME（该函数无论如何都会添加括号。
使用时需要正确传递参数sp_executesql。动态范围内不自动存在外部参数和变量，您需要传递它们。
不需要插入表变量，您可以直接从动态代码中选择。
你不需要光标，你可以使用STRING_AGG

CREATE OR ALTER PROCEDURE [dbo].[udf_Reports_DefaultFilterInfoForVariantID]
    @ReportVariantID int
AS

SET NOCOUNT, XACT_ABORT ON;  -- always use these two

DECLARE @SQL nvarchar(max) = (
        -- Quotename is needed if you ever use special characters
        -- in table/column names. Spaces, reserved words etc.
        -- Other changes add apostrophes at right places.
    SELECT STRING_AGG(CAST('
SELECT
  df.ListIDTypeID,
  df.PKID,
  tpk.IDTypeDescription,
  mt.' + QUOTENAME(tpk.DisplayColumn) + ' AS ValueDisplay
FROM list.ReportAndQueryVariantDefaultFilterListInt df
INNER JOIN list.TablePrimaryKeys tpk ON df.ListIDTypeID = tpk.ListIDTypeID
INNER JOIN ' + QUOTENAME(tpk.SchemaName) + '.' + QUOTENAME(tpk.TableName) + ' mt ON df.PKID = mt.' + QUOTENAME(tpk.PKName) + '
WHERE df.ReportVariantID = @ReportVariantID
'
      AS nvarchar(max)), 'UNION ALL ')
    FROM    list.TablePrimaryKeys] tpk
    INNER JOIN list.ReportAndQueryVariantDefaultFilterListInt df ON tpk.ListIDTypeID = df.ListIDTypeID
    WHERE df.ReportVariantID = @ReportVariantID
    GROUP BY
      tpk.SchemaName,
      tpk.TableName,
      tpk.PKName,
      tpk.DisplayColumnName
);

PRINT @SQL;  -- your friend;

EXEC sp_executesql @SQL,
  N'@ReportVariantID int',
  @ReportVariantID = @ReportVariantID;

有一种更有效的方式来进行动态查询。您可以先将所有动态表合并在一起，然后再加入ReportAndQueryVariantDefaultFilterListInt等。

CREATE OR ALTER PROCEDURE [dbo].[udf_Reports_DefaultFilterInfoForVariantID]
    @ReportVariantID int
AS

SET NOCOUNT, XACT_ABORT ON;  -- always use these two

DECLARE @SQL nvarchar(max) = '
SELECT
  df.ListIDTypeID,
  df.PKID,
  tpk.IDTypeDescription,
  mt.ValueDisplay
FROM list.ReportAndQueryVariantDefaultFilterListInt df
INNER JOIN list.TablePrimaryKeys tpk ON df.ListIDTypeID = tpk.ListIDTypeID
INNER JOIN (
' + (
        -- Quotename is needed if you ever use special characters
        -- in table/column names. Spaces, reserved words etc.
        -- Other changes add apostrophes at right places.
    SELECT STRING_AGG(CAST('
    SELECT mt.' + QUOTENAME(tpk.PKName) + ', mt.' + QUOTENAME(tpk.DisplayColumn) + '
    FROM ' + QUOTENAME(tpk.SchemaName) + '.' + QUOTENAME(tpk.TableName) + ' mt
'
      AS nvarchar(max)), 'UNION ALL ')
    FROM    list.TablePrimaryKeys] tpk
    INNER JOIN list.ReportAndQueryVariantDefaultFilterListInt df ON tpk.ListIDTypeID = df.ListIDTypeID
    WHERE df.ReportVariantID = @ReportVariantID
    GROUP BY
      tpk.SchemaName,
      tpk.TableName,
      tpk.PKName,
      tpk.DisplayColumnName
  ) + '
) AS mt(PK, ValueDisplay) ON df.PKID = mt.PK
WHERE df.ReportVariantID = @ReportVariantID
';

PRINT @SQL;  -- your friend;

EXEC sp_executesql @SQL,
  N'@ReportVariantID int',
  @ReportVariantID = @ReportVariantID;

2022-07-22