我有以下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报告添加DataSet时,它在Fields部分中不提取任何字段/列.我猜它是由于动态SQL?
我怎么解决这个问题?
包含Dynamic Sql和Temp表的问题存储过程是SSRS和ORM生成器(如Linq2SQL和EF逆向工程工具)等向导的祸根.
这是因为在运行PROC之前的工具SET FMTONLY ON;
(或最近的工具sp_describe_first_result_set
),以便派生由PROC生成的结果集模式,以便可以生成ReportViewer UI的映射.但是,既没有FMTONLY ON
也没有sp_describe_first_result
实际执行PROC.
例如,该工具将执行以下操作:
SET FMTONLY ON; EXEC dbo.MyProc NULL;
一些解决方法:
手动编辑RDL/RDLC文件以插入实际结果集列名称和类型.
暂时删除真正的proc并将其替换为一个返回零行或多行的数据集,其中包含真实proc返回的实际数据类型和列名,运行向导,然后还原真正的proc.
添加SET FMTONLY OFF;
为PROC中的第一行 - 这将强制执行PROC(尽管由于工具传入null或伪参数,proc可能会失败).此外,FMTONLY
正在被弃用
在proc开始时,添加一个伪语句,它返回结果集的实际模式,包装在一个永远不会被执行的条件分支中.
这是最后一次黑客攻击的一个例子:
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
被虚拟条件所欺骗,并假定来自永不执行的分支的模式.
顺便说一下,为了你自己的理智,我建议你不要SELECT *
在你的PROC中 - 而是明确列出从中返回的所有真实列名Orders
最后,请确保您没有SET FMTONLY ON;
在proc中包含该语句(来自上面的代码!)
END - Proc GO ** SET FMTONLY ON; ** This isn't part of the Proc!