SQL Server 2005/2008 Group By statement with parameters without using dynamic SQL?

Question

Is there a way to write SQL Server Stored Procedure which to be strongly typed ( i.e. returning known result set of columns ) and having its group statement to be dynamic.

Something like:

SELECT SUM( Column0 ) FROM Table1
GROUP BY @MyVar

I tried the following also:

SELECT SUM( Column0 ) FROM Table1
GROUP BY CASE @MyVar WHEN 'Column1' THEN Column1 ELSE Column2

The second statement works only in scenarios where the db types of Column1 and Column2 are the same. If they are not the SQL engine throws an error saying something similar to: "Conversion failed when converting the nvarchar value 'SYSTEM' to data type [The Different TYPE]."

What can I do to achieve strong result set and yet have some dynamic part - i.e. the grouper in my case? This will be exposed to LINQ.

EDIT:

As seems you can do it, but you should NOT! Absolutely overkill. Testing showed figures of thousand times slower execution plans. And it will only get slower with bigger result sets.

Answer 1

You can group on a constant which might be useful

SELECT
    SUM(Column0),
    CASE @MyVar WHEN 'Column1' THEN Column1 ELSE '' END AS MyGrouping
FROM
    Table1
GROUP BY
    CASE @MyVar WHEN 'Column1' THEN Column1 ELSE '' END

Edit: For datatype mismatch and multiple values and this allows you to group on both columns...

SELECT
    SUM(Column0),
    CASE @MyVar WHEN 'Column1' THEN Column1 ELSE NULL END AS Column1,
    CASE @MyVar WHEN 'Column2' THEN Column2 ELSE NULL END AS Column2
FROM
    Table1
GROUP BY
    CASE @MyVar WHEN 'Column1' THEN Column1 ELSE NULL END,
    CASE @MyVar WHEN 'Column2' THEN Column2 ELSE NULL END

Answer 2

You are about to shoot yourself in the foot and are asking for a bigger bullet.

The only sensible approach to this is to separate the IF into a T-SQL flow control statement:

IF (0 = @MyVar)
 SELECT SUM(Column0) FROM Table1 GROUP BY Column1;
ELSE IF (1 = @MyVar)
  SELECT SUM(Column0) FROM Table1 GROUP BY Column2;
ESLE IF (2 = @myVar)
  SELECT SUM(Column0) FROM Table1 GROUP BY Column3;

The last thing you want from the query optimizer is to generate a plan that has to GROUP BY a condition that is determined by a variable.