SQL GroupBy for Stored Procedure caused Error

Question

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

@niksrinivas that's called an alias; it's necessary – Caius Jard Jul 20 '20 at 21:43 — Caius Jard, Jul 20 '20 at 21:43
@CaiusJard Thanks, didn't know that! – Nik Srinivas Jul 20 '20 at 21:54 — Nik Srinivas, Jul 20 '20 at 21:54

score 1 · Answer 1 · answered Jul 20 '20 at 21:46

1

You cannot parameterize a GROUP BY column. Remove the parameter @TabGroupBy from the GROUP BY

answered Jul 20 '20 at 21:46

Caius Jard

72,509
5
49
80

But @TabGroupBy is a part of the Group By so if I remove it the query is not correct anymore. Do you have any other suggestions? Thank you. – Kevin K Jul 20 '20 at 22:06
@TabGroupBy is not a part of the group by; that error message is telling you it cannot be a part of the group by. You must remove it. In any case the variable is a fixed single value, like a string of `'Hello World'` - grouping by a constant is a non-op. Putting a column name in `@tabGroupBy` will not cause the query to group by that column any more than saying `DECLARE @tableName VARCHAR = 'tblPerson'; SELECT * FROM @tableName` will pull all rows from tblPerson - there are some places you cannot use variables.. This is one of them. You must remove it if you want this query to run. – Caius Jard Jul 20 '20 at 22:23
If you're trying to put a column name in there and hoping the query will group by it, then you can either pre-program all the columns it could be into a case when: `GROUP BY x, CASE WHEN @y = 'ChildID' THEN ChildId WHEN @y = 'ParentID' THEN ParentID ...` or you can use dynamic sql, but you cannot load identifiers into string variables in any language and then treat the variable like an identifier. Except maybe javascript ;) – Caius Jard Jul 20 '20 at 22:29

Peter B · Accepted Answer · 2020-07-22T07:58:45.757

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.

Do you see any issues with this code if an end-user could pass the parameter values? — Erik Philips, Jul 20 '20 at 22:38
Thanks for keeping me sharp on details, I have now escaped quotes + I have used QUOTENAME() where needed. Also mentioned the alternative of running dynamic SQL using parameters — Peter B, Jul 20 '20 at 22:55

SQL GroupBy for Stored Procedure caused Error

2 Answers2