Link to home
Start Free TrialLog in
Avatar of vikrantp
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
AS

BEGIN
      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
END

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
ASKER CERTIFIED SOLUTION
Avatar of drydenhogg
drydenhogg

Link to home
membership
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
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

ASKER

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