sql - 使用 SET FMTONLY 的 SSRS 中没有动态 SQL 存储过程的字段

Question

我有以下 SP，它在单独运行时可以正常工作:

USE [Orders]
GO
SET FMTONLY OFF; 

CREATE PROCEDURE [dbo].[Get_Details_by_Type]

@isArchived varchar(10),
@Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @sqlQuery nvarchar(max)
          IF(@isArchived = 'ALL')
            BEGIN
                set @sqlQuery  = 'SELECT *  FROM [dbo].[Orders] 
              WHERE ' + @Type + ' != € 
                ORDER BY [IDNumber]'
                exec sp_executesql @sqlQuery
            END
        ELSE
            BEGIN
            set @sqlQuery  = 'SELECT * FROM [dbo].[Orders] 
          WHERE ' + @Type + ' != € AND [isArchived] = ' + @isArchived + ' ORDER BY [IDNumber]'
            exec sp_executesql @sqlQuery
        END
END
SET FMTONLY ON; 

我遇到的问题是，当我为 SSRS 报告添加数据集时，它在“字段”部分中没有提取任何字段/列。我猜这是由于动态 SQL 造成的？

我该如何解决这个问题？

Answer 1

问题
包含动态 Sql 和临时表的存储过程是 SSRS 等向导和 Linq2SQL 和 EF 逆向工程工具等 ORM 生成器的祸根。

这是因为工具 SET FMTONLY ON; (或者最近的 sp_describe_first_result_set )在运行 PROC 之前，为了导出 PROC 生成的结果集模式，以便映射可以生成 ReportViewer UI。但是，FMTONLY ON 和 sp_describe_first_result 都没有实际执行 PROC。

例如该工具将执行以下操作:

SET FMTONLY ON;
EXEC dbo.MyProc NULL;

一些解决方法:

• 手动编辑 RDL/RDLC 文件以插入实际结果集列名称和类型。
• 暂时删除真实过程，并将其替换为返回零或多行数据集的数据集，该数据集具有真实过程返回的实际数据类型和列名称，运行向导，然后恢复真实过程。<
• 暂时添加 SET FMTONLY OFF; 作为 PROC 中的第一行 - 这将强制执行 PROC。完成后恢复原始 PROC(尽管请注意，您的 proc 可能会因工具传入的空参数或虚拟参数而失败)。另外，FMTONLY 是 being deprecated
• 在过程开始时，添加一个虚拟语句，该语句返回结果集的实际架构，包装在一个永远不会执行的条件分支中。

这是最后一次黑客攻击的示例:

CREATE PROCEDURE [dbo].[Get_Details_by_Type]
  @isArchived varchar(10),
  @Type varchar(50)
AS
BEGIN
   -- For FMTONLY ON tools only
   IF 1 = 2
     BEGIN
       -- These are the actual column names and types returned by the real proc
       SELECT CAST('' AS NVARCHAR(20)) AS Col1, 
              CAST(0 AS DECIMAL(5,3)) AS Col2, ...
     END;
-- Rest of the actual PROC goes here

FMTONLY ON/sp_describe_first_result_set 被虚拟条件愚弄，并采用从未执行的分支的模式。

顺便说一句，为了您自己的理智，我建议您不要在 PROC 中 SELECT * - 而是明确列出从 Orders 返回的所有真实列名称>

最后，请确保您的过程中不包含 SET FMTONLY ON; 语句(来自上面的代码!)

END - Proc
GO **
SET FMTONLY ON; ** This isn't part of the Proc!