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?
Best Answer
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
Or if your needs were more complex (e.g. same shape resultset and using same source but different grouping conditions)
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.