6

I have a stored procedure:

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

Now all these different cases for different values of @calcType seem to be wasting a lot of space and causing me to copy and paste all over, which always sends shivers down my spine.

Is there some way of declaring a function for CalcField, similar to lambda notation in C#, to make my code more compact and maintainable? I would want to do something like this:

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

Obviously the syntax here doesn't work, but is there something that will achieve what I want?

  • (1) Is the datatype of CalcField the same in all cases? (2) Your current queries aren't valid without GROUP BY. Are you grouping by the same thing in all cases? – Martin Smith Dec 14 '14 at 12:44
  • @MartinSmith (1) yes, CalcField always has the same data type, and (2) good catch; I've edited the question. Yes, it will always have the same group by. – Shaul Behr Dec 14 '14 at 12:48
5

No this isn't possible.

A permanent TVF or view isn't an option due to the reference to #MyTempTable

I've seen a connect item request for temporary views and agree sometimes they would be useful. This was closed as duplicate of one requesting Module-level table expressions.

You might be able to rewrite as

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

Or if your needs were more complex (e.g. same shape resultset and using same source but different grouping conditions)

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 ) 

With the last one in particular you might consider OPTION (RECOMPILE) to simplify the plan. (Likely it would have a filter with a start up predicate without the hint and not actually execute the redundant branches but you would need to check. Also row estimates could be wrong with this approach if the startup predicate is retained).

If neither of those suited you would need to get into the realms of dynamic SQL.

3

Strictly speaking (T-SQL subroutine): No.

Technically speaking (a means to abstract formulas to be defined once): Yes.

Pragmatically speaking: It depends :).

Here are the issues that are currently impeding you regarding restrictions on T-SQL functions:

  • They cannot be declared dynamically
  • They cannot access temporary tables
  • They cannot be aggregate functions (which is really what you are looking for)

However, this can all be done in SQLCLR (well, not the dynamic part, but that doesn't seem to be the focus here). Using SQLCLR you can create a function that can access a temp table, and it can even be an aggregate function. Of course, for simplistic computations such as SUM and AVG you might lose out on performance more than you gain on reducing code duplication, but that is a matter of testing (hence a large part of why "It depends").

Now in this specific case, it does not even seem like access to a temporary table is needed as the per-row values would naturally be sent into the User-Defined Aggregate. Assuming dbo.DynamicCalc has a signature of 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;

or:

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;

Or you could pass t.Factor in by itself as the @Amount param and add an additional param for @Type INT which will receive t.Type which will only be used if @CalcType = 3.

Again, whether or not to take this approach is a matter of practicality depending heavily on what formulas you have. The suggestion by @Martin to do the CASE @calcType statement to switch between formulas will be more efficient if the formulas are simple enough (as they appear to be based on the code shown in the question). But if those formulas get quite complex, or if you truly do need access to a temp table, then this is an option to consider.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.