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

Question

Is there a way to write a SQL Server stored procedure that must be strongly typed (that is, return a known set of column results) and have a dynamic expression of its group.

Sort of:

SELECT SUM( Column0 ) FROM Table1 GROUP BY @MyVar 

I also tried the following:

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

The second statement only works in scenarios where the db types of column 1 and column 2 are the same. If they are not an SQL engine, an error occurs that says something like this: "Conversion error when converting nvarchar" SYSTEM "value to data type [The Different TYPE]."

What can I do to achieve a strong set of results and, nevertheless, have some dynamic part, that is, a group in my case? This will be displayed in LINQ.

EDIT:

It seems you can do it, but you should NOT! Absolutely bust. Testing showed numbers a thousand times slower execution plans. And it will only work more slowly with large sets of results.

Answer 1

You can group a constant that may 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: to mismatch data types and multiple values, and this allows you to group 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 going to shoot a leg and ask for more bullets.

The only sensible approach to this is to split 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 a GROUP BY value, which is defined by the variable.