In SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large
GROUP BY ROLLUP. I would like to have a stored procedure that has a parameter that specifies which aggregation function to use to describe the groupings in a way that does not risk SQL injection and takes advantage of compilation (it is a heavy stored procedure).
My thoughts are to use a collection of queries that summarize the data's groupings on a particular aggregate function. (e.g. agg.DataMin, agg.DataMedian, agg.DataWeightedAverage, and so on). Then use these with the parameter in a CTE
WITH AggData AS ( SELECT * FROM agg.DataMin WHERE @AggFunction = 1 UNION ALL SELECT * FROM agg.DataMedian WHERE @AggFunction = 2 UNION ALL SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3 ) SELECT ...
My concerns are query performance and industry best practice. The data table is of a reasonable size (2+ Gig). I will have to add many aggregate queries with some being inline table-valued functions for some leave-out aggregations.
In the above, will the queries/table-valued functions only execute when the
@AggFunction matches the
WHERE condition or will they all execute and filter after the results are returned? If the latter, is there a method to short-circuit the evaluation of the unneeded queries at run-time? Also, is there some standard method to perform this in SQL that I have overlooked?