Get an ADO recordset from dynamic SQL

Question

I need to be able to run a dynamic query within SQL Server, and store the results in an ADO recordset (in a VBA application). I've read that it's impossible to run dynamic SQL in a function (even a multi-statement function) - is this correct? I've also read that it's impossible to return a table variable from a stored procedure - is this also correct? If so, how do I do this?

The only ways I can think of involve:

1. having a permanent table, inserting into it with a stored procedure, and then running a function to get the results, or
2. querying the information I need to create the dynamic SQL into ADO, using VBA to generate the SQL string, and then querying that separately.

Neither of these are ideal. What I'd really like to have is this:

-- =============================================
-- 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

Is there any way to accomplish this and return a recordset with a single ADO call?

Answer 1

You can't execute dynamic SQL in functions. But you can with stored procedures.

There are other issues also:

• You have extra brackets before and after QUOTENAME (that function will add the brackets anyway.
• You need to pass parameters properly in using sp_executesql. Outer parameters and variables do not exist in the dynamic scope automatically, you need to pass them.
• Inserting into a table variable is not necessary, you can select straight out of the dynamic code.
• You don't need the cursor, you can just use 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;

There is a more efficient way of doing the dynamic query. You can union all the dynamic tables together first, then join ReportAndQueryVariantDefaultFilterListInt etc afterwards.

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;