主页 / dba / 问题 / 86141
Accepted
Shaul Behr
Asked: 2014-12-15 04:33:50 +0800 CST

是否可以在存储过程中定义函数？

  • 772

我有一个存储过程：

create proc sp_MyProc(@calcType tinyint) as
begin
    -- some stuff collating data into #MyTempTable

    if (@calcType = 1) -- sum
        select A, B, C, CalcField = sum(Amount) 
        from #MyTempTable t 
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    else if (@calcType = 2) -- average
        select A, B, C, CalcField = avg(Amount) 
        from #MyTempTable t 
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    else if (@calcType = 3) -- some other fancy formula
        select A, B, C, CalcField = sum(case when t.Type = 1 then 1 else 0 end) * t.Factor 
        from #MyTempTable t 
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    -- plus a whole bunch of other, similar cases
    else    
        select A, B, C, CalcField = 0.0
        from #MyTempTable t 
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
end

现在，@calcType 的不同值的所有这些不同情况似乎都在浪费大量空间并导致我到处复制和粘贴，这总是让我脊背发凉。

是否有某种方法可以为 CalcField 声明一个函数，类似于 C# 中的 lambda 表示法，以使我的代码更加紧凑和可维护？我想做这样的事情：

declare @func FUNCTION(@t #MyTempTable) as real -- i.e. input is of type #MyTempTable and output is of type real

if (@calcType = 1) -- sum
    set @func = sum(@t.Amount)
else if (@calcType = 2) -- average
    set @func = avg(@t.Amount)
else if (@calcType = 3) -- some other fancy formula
    set @func = sum(case when @t.Type = 1 then 1 else 0 end) * @t.Factor 
-- plus a whole bunch of other, similar cases
else    
    set @func = 0;

select A, B, C, CalcField = @func(t) 
from #MyTempTable t 
join AnotherTable a on t.Field1 = a.Field1;
group by A, B, C

显然这里的语法不起作用，但是有什么可以实现我想要的吗？

stored-procedures sql-server-2012

2 个回答

  1. Best Answer

    不，这是不可能的。

    永久 TVF 或视图不是一个选项，因为参考#MyTempTable

    我已经看到了临时视图的连接项目请求，并且同意有时它们会很有用。这是作为一个请求模块级表表达式的副本而关闭的。

    你也许可以重写为

    SELECT A,
       B,
       C,
       CASE @calcType
         WHEN 1
           THEN sum(Amount)
         WHEN 2
           THEN avg(Amount)
       END
FROM   #MyTempTable t
       JOIN AnotherTable a
         ON t.Field1 = a.Field1
GROUP  BY A,
          B,
          C

    或者，如果您的需求更复杂（例如相同形状的结果集并使用相同的源但不同的分组条件）

    WITH Base
     AS (SELECT A,
                B,
                C,
                Factor,
                Type
         FROM   #MyTempTable t
                JOIN AnotherTable a
                  ON t.Field1 = a.Field1) 
SELECT A,
       B,
       C,
       sum(Amount)
FROM   Base
WHERE  @calcType = 1
GROUP  BY A,
          B,
          C
UNION ALL
SELECT A,
       B,
       C,
       CalcField = 0.0 * t.Factor
FROM   Base
WHERE  @calcType NOT IN ( 1, 2, 3 )

    特别是最后一个，您可能会考虑OPTION (RECOMPILE)简化计划。（很可能它会有一个带有启动谓词的过滤器，没有提示，并且不会实际执行冗余分支，但您需要检查。如果保留启动谓词，这种方法的行估计也可能是错误的）。

    如果这些都不适合，您将需要进入动态 SQL 领域。

    • 6

  2. 严格来说（T-SQL 子程序）：没有。

    从技术上讲（一种抽象公式的方法要定义一次）：是的。

    务实地说：这取决于:)。

    以下是目前阻碍您对 T-SQL 函数的限制的问题：

    • 它们不能动态声明
    • 他们无法访问临时表
    • 它们不能是聚合函数（这正是您要寻找的）

    然而，这一切都可以在 SQLCLR 中完成（嗯，不是动态部分，但这似乎不是这里的重点）。使用 SQLCLR，您可以创建一个可以访问临时表的函数，它甚至可以是一个聚合函数。当然，对于简单的计算，例如SUM，AVG您可能会失去性能而不是减少代码重复所获得的收益，但这是一个测试问题（因此很大一部分原因是“它取决于”）。

    现在在这种特定情况下，似乎不需要访问临时表，因为每行值自然会发送到用户定义的聚合中。假设 dbo.DynamicCalc 的签名为DynamicCalc(@CalcType TINYINT, @Amount FLOAT)：

    select A, B, C, CalcField = dbo.DynamicCalc(2, t.Amount) 
from #MyTempTable t 
join AnotherTable a
    on t.Field1 = a.Field1
group by A, B, C;

    或者：

    select A, B, C, CalcField = dbo.DynamicCalc(3, IIF(t.Type = 1, t.Factor, 0)) 
from #MyTempTable t 
join AnotherTable a
    on t.Field1 = a.Field1
group by A, B, C;

    或者您可以单独t.Factor作为@Amount参数传入并添加一个额外的参数，@Type INT该参数将接收t.Type，仅在@CalcType= 3 时使用。

    同样，是否采用这种方法是实用性问题，很大程度上取决于您拥有的公式。CASE @calcType如果公式足够简单（因为它们似乎基于问题中显示的代码），@Martin 建议在公式之间切换语句会更有效。但是，如果这些公式变得相当复杂，或者您确实需要访问临时表，那么这是一个可以考虑的选项。

    • 5

相关问题