sql-server - sp_executesql 按 "must contain at least one column that is not an outer reference"分组

Question

在简化版本中，我正在尝试做类似的事情:

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

但我明白:

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

如果我不参数化它，它就会工作，意味着

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

Answer 1

通过使用参数化查询，您无法实现您想要做的事情，唯一的方法是简单的动态查询

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

因为变量只是在动态查询中充当值。考虑以下示例。

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 

根据你的说法，答案应该是2015，但结果是

DATEPART(year, getdate())