我有以下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引起的吗?
我该如何解决?
包含Dynamic Sql和Temp表 的问题 存储proc是诸如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;
sp_describe_first_result_set
FMTONLY ON
sp_describe_first_result
例如,该工具将执行以下操作:
SET FMTONLY ON; EXEC dbo.MyProc NULL;
一些解决方法:
SET FMTONLY OFF;
FMTONLY
这是最近一次黑客攻击的示例:
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
SELECT *
Orders
最后,只需确保您没有SET FMTONLY ON;在proc中包含该语句(从上面的代码中即可!)
END - Proc GO ** SET FMTONLY ON; ** This isn't part of the Proc!