sp_executesql Group by "must contain at least one column that is not an outer reference"

Question

In a simplified version I'm trying to do something like that :

Set @datepart = 'DATEPART(year, myDate)' 
Set @SQLQuery = 'SELECT @datepart AS TIME 
                 FROM someTable 
                 GROUP BY @datepart'
Execute sp_executesql @SQLQuery, N'@datepart nvarchar(1000)', @datepart

But I get :

Each GROUP BY expression must contain at least one column that is not an outer reference.

It is working if I does not parameterize it, means

Set @SQLQuery = 'SELECT ' + @datepart + ' AS TIME 
                 FROM someTable 
                 GROUP BY ' + @datepart
Execute sp_executesql @SQLQuery

Answer 1

By using parametrized query you cannot achieve what you are trying to do only way is simple dynamic query

Set @SQLQuery = 'SELECT ' + @datepart + ' AS TIME 
                 FROM someTable 
                 GROUP BY ' + @datepart
Execute sp_executesql @SQLQuery

because the variable just act as value inside dynamic query. Consider the following example.

DECLARE @datepart NVARCHAR(1000),
        @SQLQuery NVARCHAR(max)

SET @datepart = 'DATEPART(year, getdate())'
SET @SQLQuery = 'SELECT @datepart AS TIME'

EXECUTE Sp_executesql
  @SQLQuery,
  N'@datepart nvarchar(1000)',
  @datepart 

According to you the answer should be 2015 but the result is

DATEPART(year, getdate())