Link to home
Start Free TrialLog in
Avatar of vikrantp

asked on

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

I have this stored procedure as below
ALTER PROCEDURE [dbo].[spLoaderCoverageObjectTypeActionTypeWithOptional]
      -- Add the parameters for the stored procedure here
      @ObjectClass sysname,
    @ObjectType sysname,
      @RecordAction sysname,
      @FieldName sysname

      SELECT DISTINCT @ObjectClass as ObjectClass,@ObjectType as ObjectType,@RecordAction as RecordAction,@FieldName as FieldName, COUNT(*) AS VALIDCount--,
      /*(select Count(*) from TestData6061.dbo.accounting WHERE (AutomationType LIKE 'loader') AND ([Negative Testcase] = 0) AND
      (@ObjectType = @ObjectType) AND (@RecordAction = 'New') AND ('['+@FieldName+'] ' IS NULL)
      GROUP BY [01-RecordClass],[02-RecordAction]) as NULLCount*/

      FROM         TestData6061.dbo.accounting
      WHERE     (AutomationType LIKE 'loader') AND ([Negative Testcase] = 0) AND (@ObjectType = @ObjectType) AND (@RecordAction = 'New') AND
                      ('['+@FieldName+'] ' IS NOT NULL)
      GROUP BY [01-RecordClass],[02-RecordAction],@FieldName

I call the stored procedure as follows -
exec spLoaderCoverageObjectTypeActionTypeWithOptional 'accounting','AccountingParameters','New','13-OverdueOpeningTargetAccount'

Now if I remove the @FieldName from Group By clause it runs but the output is not what I want. If I run the group by clause with
GROUP BY [01-RecordClass], [02-RecordAction], [13-OverdueOpeningTargetAccount] it works fine but only when I use it in the stored procedure with @FieldName it doesn't. Is there any solution / workaround for this? Thanks in advance
Avatar of drydenhogg

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of drydenhogg

Oh missed a few of the field names being passed into the select, but you can see the principle of it.

Firt two lines should of been:

 SET @SQL="SELECT DISTINCT " + @ObjectClass + " as ObjectClass, " + @ObjectType + " as ObjectType, " + @RecordAction " + as "
SET @SQL=@SQL + "RecordAction," + @FieldName + " as FieldName, COUNT(*) AS VALIDCount "
Avatar of vikrantp


excellent. That worked with a couple of changes. Thanks a lot for the help.