sql-server - 如何避免从 SQL Server 中的 UDF 调用存储过程

Question

在开始之前，我知道你不能从 UDF 调用存储过程，而且我知道这有各种“原因”(但对我来说没有多大意义，老实说，这听起来像是 Microsoft 的懒惰)部分)。

我更感兴趣的是如何设计一个系统来解决 SQL Server 中的这个缺陷。

这是我当前拥有的系统的快速概述:

• 我有一个动态报告生成器，用户可以在其中指定数据项、运算符(=、<、!= 等)和过滤器值。这些用于通过一个或多个过滤器构建“规则”，例如我的规则可能有两个过滤器“类别 < 12”和“位置!= 'York'”；

• 这些“规则”有成千上万条，其中一些有很多很多过滤器；

• 每个规则的输出都是一个法定报告，始终具有完全相同的“形状”，即相同的列/数据类型。基本上这些报告会生成吨位和 Material list ；

• 我有一个标量值函数，可以为指定规则生成动态 SQL，并将其作为 VARCHAR(MAX) 返回；

• 我有一个被调用来运行特定规则的存储过程，它调用 UDF 来生成动态 SQL，运行它并返回结果(这过去只返回结果，但现在我存储输出在进程键控表中，以使数据更易于共享，因此我返回此数据的句柄)；

• 我有一个存储过程，调用它来运行特定公司的所有规则，因此它会列出要运行的规则列表，按顺序运行它们，然后将结果合并在一起作为输出。

所以这一切都很完美。

现在我想要最后一件事，一份运行公司摘要的报告，然后将成本应用于吨位/ Material 以生成成本报告。当我上周开始这样做时，这似乎是一个简单的要求:'(

我的报告必须是表值函数，才能与我已经编写的报告代理系统配合使用。如果我将其编写为存储过程，那么它将不会通过我的报告代理运行，这意味着它将不受控制，即我不知道谁运行了该报告以及何时运行。

但是我无法从表值函数中调用存储过程，处理此问题的两种明显方法如下:

1. 获取 SQL 创建输出，运行它并获取结果。

   --Method #1
   WHILE @RuleIndex <= @MaxRuleIndex
   BEGIN
   DECLARE @DSFId UNIQUEIDENTIFIER;
   SELECT @DSFId = [GUID] FROM NewGUID; --this has to be deterministic, it isn't but the compiler thinks it is and that's good enough :D
   DECLARE @RuleId UNIQUEIDENTIFIER;
   SELECT @RuleId = DSFRuleId FROM @DSFRules WHERE DSFRuleIndex = @RuleIndex;
   DECLARE @SQL VARCHAR(MAX);
   
   --Get the SQL
   SELECT @SQL = DSF.DSFEngine(@ServiceId, @MemberId, @LocationId, @DSFYear, NULL, NULL, NULL, NULL, @DSFId, @RuleId);
   
   --Run it
   EXECUTE(@SQL);
   
   --Copy the data out of the results table into our local copy
   INSERT INTO 
       @DSFResults 
   SELECT 
       TableId, TableCode, TableName, RowId, RowName, LocationCode, LocationName, ProductCode, ProductName, PackagingGroupCode, PackagingGroupName, LevelName, WeightSource, Quantity, Paper, Glass, Aluminium, Steel, Plastic, Wood, Other, 0 AS General 
   FROM 
       DSF.DSFPackagingResults 
   WHERE 
       DSFId = @DSFId 
       AND RuleId = @RuleId;
   SELECT @RuleIndex = @RuleIndex + 1;
   END;
   

2. 直接调用报告

   --Method #2
   WHILE @RuleIndex <= @MaxRuleIndex
   BEGIN
    DECLARE @DSFId UNIQUEIDENTIFIER;
   SELECT @DSFId = [GUID] FROM NewGUID; --this has to be deterministic, it isn't but the compiler thinks it is :D
   DECLARE @RuleId UNIQUEIDENTIFIER;
   SELECT @RuleId = DSFRuleId FROM @DSFRules WHERE DSFRuleIndex = @RuleIndex;
   DECLARE @SQL VARCHAR(MAX);
   
   --Run the report
   EXECUTE ExecuteDSFRule @ServiceId, @MemberId, @LocationId, @DSFYear, NULL, NULL, NULL, @RuleId, @DSFId, 2;
   
   --Copy the data out of the results table into our local copy
   INSERT INTO 
       @DSFResults 
   SELECT 
       TableId, TableCode, TableName, RowId, RowName, LocationCode, LocationName, ProductCode, ProductName, PackagingGroupCode, PackagingGroupName, LevelName, WeightSource, Quantity, Paper, Glass, Aluminium, Steel, Plastic, Wood, Other, 0 AS General 
   FROM 
       DSF.DSFPackagingResults 
   WHERE 
       DSFId = @DSFId 
       AND RuleId = @RuleId;
   SELECT @RuleIndex = @RuleIndex + 1;
   END;
   

我可以想到以下解决方法(没有一个特别令人满意):

• 在 CLR 中重写其中的一些内容(但这只是破坏规则的一大堆麻烦)；

• 使用存储过程来生成我的报告(但这意味着我失去了对执行的控制，除非我为这个单一报告开发一个新系统，这与几十个都工作正常的现有报告不同)；

• 将执行与报告分开，因此我有一个进程来执行报告，另一个进程只获取输出(但如果没有更多工作，就无法判断报告何时完成)；

  <

• 等到 Microsoft 明白并允许从 UDF 执行存储过程。

还有其他想法吗？

<小时/>

编辑于 2013 年 5 月 3 日，这是一个(非常)简单的示例，说明了如何将其结合在一起:

--Data to be reported
CREATE TABLE DataTable (
    MemberId INT,
    ProductId INT,
    ProductSize VARCHAR(50),
    Imported INT,
    [Weight] NUMERIC(19,2));
INSERT INTO DataTable VALUES (1, 1, 'Large', 0, 5.4);
INSERT INTO DataTable VALUES (1, 2, 'Large', 1, 6.2);
INSERT INTO DataTable VALUES (1, 3, 'Medium', 0, 2.3);
INSERT INTO DataTable VALUES (1, 4, 'Small', 1, 1.9);
INSERT INTO DataTable VALUES (1, 5, 'Small', 0, 0.7);
INSERT INTO DataTable VALUES (1, 6, 'Small', 1, 1.2);

--Report Headers
CREATE TABLE ReportsTable (
    ReportHandle INT,
    ReportName VARCHAR(50));
INSERT INTO ReportsTable VALUES (1, 'Large Products');
INSERT INTO ReportsTable VALUES (2, 'Imported Small Products');

--Report Detail
CREATE TABLE ReportsDetail (
    ReportHandle INT,
    ReportDetailHandle INT,
    DatabaseColumn VARCHAR(50),
    DataType VARCHAR(50),
    Operator VARCHAR(3),
    FilterValue VARCHAR(50));
INSERT INTO ReportsDetail VALUES (1, 1, 'ProductSize', 'VARCHAR', '=', 'Large');
INSERT INTO ReportsDetail VALUES (2, 1, 'Imported', 'INT', '=', '1');
INSERT INTO ReportsDetail VALUES (2, 1, 'ProductSize', 'VARCHAR', '=', 'Small');
GO
CREATE FUNCTION GenerateReportSQL (
    @ReportHandle INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX);
    SELECT @SQL = 'SELECT SUM([Weight]) FROM DataTable WHERE 1=1 ';
    DECLARE @Filters TABLE (
        FilterIndex INT,
        DatabaseColumn VARCHAR(50),
        DataType VARCHAR(50),
        Operator VARCHAR(3),
        FilterValue VARCHAR(50));
    INSERT INTO @Filters SELECT ROW_NUMBER() OVER (ORDER BY DatabaseColumn), DatabaseColumn, DataType, Operator, FilterValue FROM ReportsDetail WHERE ReportHandle = @ReportHandle;
    DECLARE @FilterIndex INT = NULL;
    SELECT TOP 1 @FilterIndex = FilterIndex FROM @Filters;
    WHILE @FilterIndex IS NOT NULL
    BEGIN
        SELECT TOP 1 @SQL = @SQL + ' AND ' + DatabaseColumn + ' ' + Operator + ' ' + CASE WHEN DataType = 'VARCHAR' THEN '''' ELSE '' END + FilterValue + CASE WHEN DataType = 'VARCHAR' THEN '''' ELSE '' END FROM @Filters WHERE FilterIndex = @FilterIndex;
        DELETE FROM @Filters WHERE FilterIndex = @FilterIndex;
        SELECT @FilterIndex = NULL;
        SELECT TOP 1 @FilterIndex = FilterIndex FROM @Filters;
    END;
    RETURN @SQL;
END;
GO
CREATE PROCEDURE ExecuteReport (
    @ReportHandle INT)
AS
BEGIN
    --Get the SQL
    DECLARE @SQL VARCHAR(MAX);
    SELECT @SQL = dbo.GenerateReportSQL(@ReportHandle);
    EXECUTE (@SQL);
END;
GO
--Test
EXECUTE ExecuteReport 1;
EXECUTE ExecuteReport 2;
SELECT dbo.GenerateReportSQL(1);
SELECT dbo.GenerateReportSQL(2);
GO
--What I really want
CREATE FUNCTION RunReport (
    @ReportHandle INT)
RETURNS @Results TABLE ([Weight] NUMERIC(19,2))
AS
BEGIN
    INSERT INTO @Results EXECUTE ExecuteReport @ReportHandle;
    RETURN;
END;
--Invalid use of a side-effecting operator 'INSERT EXEC' within a function

Answer 1

如果我处于你的情况，我不会尝试破解任何东西。我会像这样设置对象:

CREATE TABLE [dbo].[ReportCollection] (
    [ReportCollectionID] int,
    [ReportID] int
)

CREATE TABLE [dbo].[ReportResult] (
    [ReportID] int,
    [LocationCode] int,
    [LocationName] nvarchar(max)
)

CREATE PROCEDURE [dbo].[usp_ExecuteReport] (
    @ReportID int
)
AS
    INSERT [dbo].[ReportResult]
    SELECT @ReportID, 1, N'StackOverflow'
END

CREATE FUNCTION [dbo].[udf_RetrieveReportCollectionResults] (
    @ReportCollectionID int
)
RETURNS @Results TABLE ([ReportID], [LocationCode], [LocationName])
AS
BEGIN
    SELECT *
    FROM [dbo].[ReportResult] rr
    JOIN [dbo].[ReportCollection] rc
        ON rr.ReportID = rc.ReportID
    WHERE rc.ReportCollectionID = @ReportCollectionID
END

像这样使用它们:

INSERT [dbo].[ReportCollection] VALUES (1, 1)
INSERT [dbo].[ReportCollection] VALUES (1, 2)

EXEC [dbo].[usp_ExecuteReport] @ReportID = 1
EXEC [dbo].[usp_ExecuteReport] @ReportID = 2

SELECT * FROM [dbo].[udf_RetrieveReportCollectionResults](1)

每次运行报告时，都会开始一个新的集合。您的应用程序应该启动所有报告并随后整合结果。

--

如果您确实想要从 udf 调用存储过程(请不要这样做)，请在 xp_cmdshell 上进行搜索。