I have a quick question that why my SQL Stored Procedure did not work properly. Can someone explain what is wrong with my Stored Procedure Query? Error: “Each GROUP BY expression must contain at least one column that is not an outer reference.”
SELECT
@TabGroupBy + @TabGroupByName + ','
,SUM(Value) AS Sum
,[Unit]
,[Child_Name]
FROM (
SELECT [model_id]
,[Child_ID]
,[Property_ID]
,[DDate]
,[Hour]
,[Value]
FROM [RP_IRP].[M_PLEXOS].[dat_Generators]
where parent_ID=1 and child_ID in(9, 357,358) and Property_ID in (4,31)
) a
inner join [RP_IRP].[M_PLEXOS].[Child_Object] b on a.child_id=b.child_id
inner join [M_PLEXOS].[Property] d on d.[Property_ID] = a.[Property_ID]
inner join [M_PLEXOS].[Units] e on d.[Unit_ID]=e.[Unit_ID]
inner join [M_PLEXOS].[Model_Config] f on a.[Model_id]=f.[Model_id]
WHERE Child_Name = @SelectedChildValue AND Property = @SelectedPropertyValue
AND Unit = @SelectedUnitValue
GROUP BY Child_Name , @TabGroupBy , Unit HAVING SUM(Value) > @MinValue
Advertisement
Answer
It can never work the way you are trying it, because by definition, all column names in the SELECT-part, as well as all column names in the GROUP BY-clause can not come from @-variables. They must be written as plain text because they are identifiers.
The same applies to table names + column names in FROM and JOIN clauses.
The reason for this is that the query compiler is built to check all specified columns (and tables, and schemas, and more) against objects that exist in your database(s), and this needs to succeed before a single line of compiled code runs. You should always keep in mind that at compilation time, @-variables don’t yet exist and can’t have values (because they don’t yet exist).
The solution is to use dynamic SQL. You can achieve what you want by building the actual SQL string that you want to execute in a @SQL variable of type NVARCHAR(max), and then EXEC the contents of that variable. EXEC will invoke the SQL query compiler on the contents of its parameter.
Example code, may not be 100% perfect because I can’t run it due to not having your database available, but this should get you on your way:
DECLARE @SQL NVARCHAR(max) =
'SELECT ' +
QUOTENAME(@TabGroupBy) + ' AS ' + QUOTENAME(@TabGroupByName) + ', ' +
'SUM(Value) AS Sum, ' +
'[Unit], ' +
'[Child_Name] ' +
'FROM ( ' +
'SELECT [model_id],[Child_ID],[Property_ID],[DDate],[Hour],[Value] ' +
'FROM [RP_IRP].[M_PLEXOS].[dat_Generators] ' +
'where parent_ID = 1 ' +
' and child_ID in (9, 357, 358) ' +
' and Property_ID in (4, 31) ' +
') a ' +
'inner join [RP_IRP].[M_PLEXOS].[Child_Object] b on a.child_id=b.child_id ' +
'inner join [M_PLEXOS].[Property] d on d.[Property_ID] = a.[Property_ID] ' +
'inner join [M_PLEXOS].[Units] e on d.[Unit_ID]=e.[Unit_ID] ' +
'inner join [M_PLEXOS].[Model_Config] f on a.[Model_id]=f.[Model_id] ' +
'WHERE Child_Name = ''' + REPLACE(@SelectedChildValue , '''', '''''') + ''' ' +
' AND Property = ''' + REPLACE(@SelectedPropertyValue, '''', '''''') + ''' ' +
' AND Unit = ''' + REPLACE(@SelectedUnitValue , '''', '''''') + ''' ' +
'GROUP BY Child_Name , ' + QUOTENAME(@TabGroupBy) + ', Unit ' +
'HAVING SUM(Value) > ' + CAST(@MinValue AS VARCHAR(20)) -- assuming @MinValue is INT or FLOAT
EXEC (@SQL)
This code assumes there may be quotes inside the @-variables. Always use REPLACE to double embedded quotes if the @-variables represent string values, or even better: use dynamic SQL along with @-parameters, see this Q & A for how that can be done.
For cases where the @-variables represent database identifiers (column names etc.), you need to use QUOTENAME(...) as in the example code to make sure that no abuse can take place.