0

I keep getting this error when running my stored procedure:

Column '#objects.TableName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I understand the error itself, but it doesn't make sense in my case, as the temp table itself doesn't have any aggregation, AND I have this exact temp table contained in another stored procedure and it runs there without a problem. The temp table is created without a problem outside of the stored procedure.

CREATE TABLE #objects
             (
                 ObjectId          INT
                 ,SchemaName       NVARCHAR(20)
                 ,TableName        NVARCHAR(50)
                 ,ColumnName       NVARCHAR(100)
                 ,ObjectName       NVARCHAR(150)
                 ,Type             NVARCHAR(50)
                 ,ObjectDefinition NVARCHAR(MAX)
                 ,IndexName        NVARCHAR(100)
                 ,IndexType        NVARCHAR(50)
                 ,IsPrimaryKey     BIT
                 ,IsUniqueKey      BIT
             );
        INSERT INTO #objects (
            ObjectId
            ,SchemaName
            ,TableName
            ,ColumnName
            ,ObjectName
            ,Type
            ,ObjectDefinition
            ,IndexName
            ,IndexType
            ,IsPrimaryKey
            ,IsUniqueKey
        )
        SELECT ObjectId = a.object_id
               ,SchemaName = SCHEMA_NAME ( a.schema_id )
               ,TableName = e.name
               ,ColumnName = COL_NAME ( b.parent_object_id, b.parent_column_id )
               ,ObjectName = OBJECT_NAME ( a.object_id )
               ,Type = a.type_desc
               ,ObjectDefinition = CAST(OBJECT_DEFINITION ( a.object_id ) AS NVARCHAR(MAX))
               ,IndexName = ch.name
               ,IndexType = ch.type_desc
               ,IsPrimaryKey = NULL
               ,IsUniqueKey = NULL
        FROM   sys.objects AS a
               LEFT JOIN sys.default_constraints AS b
                   ON a.object_id = b.object_id
               LEFT JOIN sys.check_constraints AS c
                   ON a.object_id = c.object_id
               LEFT JOIN sys.tables AS e
                   ON e.object_id = a.parent_object_id
               LEFT JOIN sys.indexes AS ch
                   ON a.object_id = ch.object_id
        WHERE  a.type_desc NOT IN ( 'INTERNAL_TABLE'
                                    ,'SYSTEM_TABLE'
                                    ,'SERVICE_QUEUE'
                                    ,'SQL_STORED_PROCEDURE'
                                    ,'SQL_INLINE_TABLE_VALUED_FUNCTION'
                                    ,'SQL_TABLE_VALUED_FUNCTION'
                                    ,'PRIMARY_KEY_CONSTRAINT'
                                    ,'UNIQUE_CONSTRAINT' )
               AND ch.type_desc IN ( NULL
                                     ,'CLUSTERED COLUMNSTORE' )
        UNION ALL
        SELECT ObjectId = ix.object_id
               ,SchemaName = SCHEMA_NAME ( tab.schema_id )
               ,TableName = OBJECT_NAME ( tab.object_id )
               ,ColumnName = col.name
               ,ObjectName = ix.name
               ,Type = ix.type_desc
               ,ObjectDefinition = NULL
               ,IndexName = ix.name
               ,IndexType = ix.type_desc
               ,IsPrimaryKey = ix.is_primary_key
               ,IsUniqueKey = ix.is_unique_constraint
        FROM   sys.tables AS tab
               INNER JOIN sys.indexes AS ix
                   ON tab.object_id = ix.object_id
               INNER JOIN sys.index_columns AS ic
                   ON ic.object_id = ix.object_id
                      AND ic.index_id = ix.index_id
               INNER JOIN sys.columns AS col
                   ON ix.object_id = col.object_id
                      AND col.column_id = ic.column_id
        WHERE  ix.is_primary_key = 1
               OR ix.is_unique_constraint = 1
               OR ix.type IN ( 1
                               ,2 )
        ORDER BY SchemaName
                 ,Type;

I only need to solve the error, other things in the stored procedure I don't have a problem with.

Susie
  • 23
  • 4
  • 3
    `ch.type_desc IN ( NULL,'CLUSTERED COLUMNSTORE' )` is going to fail spectacularly, apart from any other problems. One may not compare to NULL, except by way of `x IS NULL` or `x IS NOT NULL` statements. Rewrite as `(ch.type_desc IS NULL OR ch.type_desc='CLUSTERED COLUMNSTORE')`. – TT. Apr 24 '19 at 09:15
  • 2
    That code runs for for me. That SQL we have there isn't the problem (apart from the point @TT. found with their very keen eyes (nice spot!)). There is also no `GROUP BY` or aggregate functions there, further confirming that the above isn't the problem, – Thom A Apr 24 '19 at 09:15
  • I *suppose* the OP might have `SET ANSI_NULLS OFF` @TT., but I very much hope not. – Thom A Apr 24 '19 at 09:17
  • 2
    This code may cause a runtime "String or binary data would be truncated" due to `TableName NVARCHAR(50)` etc (object names may be [up to 128 characters](https://stackoverflow.com/q/5720212/11683)), but it will not cause that compile time grouping error. – GSerg Apr 24 '19 at 09:18
  • 1
    If you want to store the names of object names, use the datatype `sysname`, which is a synonym for `nvarchar(128)`. – Thom A Apr 24 '19 at 09:20
  • Susie, as others have pointed out the problem you face is with another query than the one you have in the question. Can you [edit] your question and add the query that is the cause of the problem? – TT. Apr 24 '19 at 09:21
  • I actually do have `SET ANSI_NULLS OFF` put in, it worked well so I didn't see a problem there.. also when I commented out everything else besides the temp table above, it still threw the same error. Which baffles me is the fact that I have the same temp table in another stored proc and it works just fine – Susie Apr 24 '19 at 09:27
  • 1
    Setting ansi nulls off is deprecated, and will be removed from future versions of SQL Server (see [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017) - the important note at the start). It is ill-advised to do so. In any case, the problem is not with the query you posted. – TT. Apr 24 '19 at 09:29
  • I also just ran the complete query you have in your question: no problems, no errors reported. – TT. Apr 24 '19 at 09:31
  • 1
    I really hoped my silly comment about `SET ANSI_NULLS OFF` was wrong. You need to change that and treat NULLs properly by using `IS (NOT) NULL` syntax. – Thom A Apr 24 '19 at 09:31
  • Ok, I did change it, thank you for the advise. The whole stored procedure is really long, I guess I will try finding the culprit elsewhere, good to know that the stored procedure itself should not throw such error. Thank you – Susie Apr 24 '19 at 09:37

0 Answers0