Sp_executesql Group "must contain at least one column that is not an external reference"
In a simplified version, I am trying to do something like this:
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 am getting:
Each GROUP BY expression must contain at least one column that is not an xref.
It works if I don't parameterize it means
Set @SQLQuery = 'SELECT ' + @datepart + ' AS TIME
FROM someTable
GROUP BY ' + @datepart
Execute sp_executesql @SQLQuery
source to share
By using parameterized query, you cannot achieve what you are trying to do, it is just a dynamic query
Set @SQLQuery = 'SELECT ' + @datepart + ' AS TIME
FROM someTable
GROUP BY ' + @datepart
Execute sp_executesql @SQLQuery
because the variable just acts like a value inside a dynamic request. 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
In your opinion, the answer should be 2015
, but the result is
DATEPART(year, getdate())
source to share
What no one else understood is that you asked the question and solved it yourself. SQL Server quite rightly noted that you are trying to group something, but you are not reporting an exit from it.
I took your query and made a version, almost all of us should be able to use on SQL 2005+:
DECLARE @datepart NVARCHAR(MAX), @SQLQUERY NVARCHAR(MAX)
SEt @datepart = 'DATEPART(year, myDate)'
Set @SQLQuery = 'SELECT @datepart AS TIME
FROM sys.objects
GROUP BY @datepart'
Execute sp_executesql @SQLQuery, N'@datepart nvarchar(1000)', @datepart
Now it will explode as expected. The reason is that @datepart is a string value. He is actually trying to:
SELECT 'String' FROM 'Table' GROUP BY 'String'
In no case is it necessary for the table to answer this question. SQL confuses him. What you need to do in order to achieve what you are trying to do is simply concatenate the strings together, however this is not expected:
DECLARE @datepart NVARCHAR(MAX), @SQLQUERY NVARCHAR(MAX)
SEt @datepart = 'DATEPART(year, create_date)'
Set @SQLQuery = 'SELECT ' + @datepart +' AS TIME
FROM sys.objects
GROUP BY '+ @datepart
Execute sp_executesql @SQLQuery
Or in your specific case:
DECLARE @datepart NVARCHAR(MAX), @SQLQUERY NVARCHAR(MAX)
SEt @datepart = 'DATEPART(year, myDate)'
Set @SQLQuery = 'SELECT ' + @datepart +' AS TIME
FROM someTable
GROUP BY '+ @datepart
Execute sp_executesql @SQLQuery
If you're committing code like this, I would recommend typing a resume. This is bad practice.
source to share
if you REPLACE
query at runtime with a variable then you can achieve 2015
dynamically
DECLARE @datepart NVARCHAR(100),
@SQLQuery NVARCHAR(max)
SET @datepart = 'DATEPART(year, getdate())'
SET @SQLQuery = 'SELECT @datepart AS TIME'
SET @SQLQuery = REPLACE(@SQLQuery, '@datepart', @datepart)
exec (@SQLQuery);
source to share
@tinka this works great. But I just checked with another example and it doesn't seem to help against SQLinjection. Perhaps your solution is the same as a normal concatenated query?
DECLARE @SQLQueryInnen nvarchar(max)
DECLARE @ParameterDefinition nvarchar(max)
DECLARE @aufnr nvarchar(200)
SET @aufnr = '1234; SELECT * FROM sensitiveTable'-- here the attacker inserted an SQL Statement in aufnr
Set @SQLQueryInnen = 'SELECT * FROM myTable WHERE aufnr = @aufnr'
Set @SQLQueryInnen = REPLACE(@SQLQueryInnen, '@aufnr', @aufnr)
Set @ParameterDefinition = '@aufnr nvarchar(200)'
Execute sp_Executesql @SQLQueryInnen, @ParameterDefinition, @aufnr
-- Result: The illegal SELECT statement gets executed!!!
If I execute this example without REPLACE, the attack won't work, but you get: Conversion failed when converting the nvarchar value '1234; SELECT * FROM qdb_tab_imp_me_q_fehler' to data type int.
source to share