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?
@AggFunction
is an expression that your code expects. – Aaron Bertrand Jun 6 '16 at 16:27SET @sql += CASE @AggFunction WHEN 1 THEN N'agg.DataMin' WHEN 2 THEN N'agg.DataMedian' WHEN 3 THEN N'agg.DataWeightedAverage' ELSE NULL END
I'm not sure how this is vulnerable to SQL injection. – Aaron Bertrand Jun 6 '16 at 16:36