Início / dba / Perguntas / 86141
Accepted
Shaul Behr
Asked: 2014-12-15 04:33:50 +0800 CST

É possível definir uma função dentro de um procedimento armazenado?

  • 772

Eu tenho um procedimento armazenado:

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

Agora, todos esses casos diferentes para valores diferentes de @calcType parecem estar desperdiçando muito espaço e me fazendo copiar e colar tudo, o que sempre me causa arrepios na espinha.

Existe alguma maneira de declarar uma função para CalcField, semelhante à notação lambda em C#, para tornar meu código mais compacto e sustentável? Eu gostaria de fazer algo assim:

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

Obviamente, a sintaxe aqui não funciona, mas há algo que alcançará o que eu quero?

stored-procedures sql-server-2012

2 respostas

  1. Best Answer

    Não isso não é possível.

    Uma TVF ou visualização permanente não é uma opção devido à referência a#MyTempTable

    Eu vi uma solicitação de item de conexão para visualizações temporárias e concordo que às vezes elas seriam úteis. Isso foi fechado como duplicata de uma expressão de tabela em nível de módulo solicitante .

    Você pode ser capaz de reescrever como

    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

    Ou se suas necessidades fossem mais complexas (por exemplo, mesmo conjunto de resultados de forma e usando a mesma fonte, mas diferentes condições de agrupamento)

    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 )

    Com o último em particular, você pode considerar OPTION (RECOMPILE)simplificar o plano. (Provavelmente teria um filtro com um predicado de inicialização sem a dica e não executaria as ramificações redundantes, mas você precisaria verificar. Também as estimativas de linha podem estar erradas com essa abordagem se o predicado de inicialização for retido).

    Se nenhum desses for adequado, você precisaria entrar nos domínios do SQL dinâmico.

    • 6

  2. Estritamente falando (sub-rotina T-SQL): Não.

    Tecnicamente falando (um meio de abstrair fórmulas a serem definidas uma vez): Sim.

    Pragmaticamente falando: Depende :).

    Aqui estão os problemas que estão impedindo você em relação às restrições das funções T-SQL:

    • Eles não podem ser declarados dinamicamente
    • Eles não podem acessar tabelas temporárias
    • Eles não podem ser funções agregadas (que é realmente o que você está procurando)

    No entanto, tudo isso pode ser feito em SQLCLR (bem, não a parte dinâmica, mas esse não parece ser o foco aqui). Usando SQLCLR você pode criar uma função que pode acessar uma tabela temporária e pode até ser uma função agregada. Claro, para cálculos simplistas como SUMe AVGvocê pode perder mais desempenho do que ganhar na redução da duplicação de código, mas isso é uma questão de teste (daí uma grande parte do motivo pelo qual "depende").

    Agora, neste caso específico, nem parece que o acesso a uma tabela temporária é necessário, pois os valores por linha seriam naturalmente enviados para o agregado definido pelo usuário. Supondo que dbo.DynamicCalc tenha uma assinatura de 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;

    ou:

    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;

    Ou você pode passar t.Factorpor si mesmo como o @Amountparâmetro e adicionar um parâmetro adicional para @Type INTo qual irá receber t.Typeque só será usado se @CalcType= 3.

    Novamente, adotar ou não essa abordagem é uma questão de praticidade, dependendo muito das fórmulas que você possui. A sugestão do @Martin de fazer a CASE @calcTypeinstrução para alternar entre as fórmulas será mais eficiente se as fórmulas forem simples o suficiente (pois parecem ser baseadas no código mostrado na pergunta). Mas se essas fórmulas ficarem bastante complexas, ou se você realmente precisar de acesso a uma tabela temporária, essa é uma opção a ser considerada.

    • 5

relate perguntas