Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'BY'

October 19, 2022

I try to run the dynamic sql query but it showing an error.I’ve googled that error(in title) but I cant seem to get it to help me, my dynamic sql query:

DROP TABLE IF EXISTS TABLE_NEW_XY
CREATE TABLE TABLE_NEW_XY (email VARCHAR(MAX),Profile VARCHAR(MAX),Subscriber 
VARCHAR(MAX),origin VARCHAR(MAX),country VARCHAR(MAX),Date VARCHAR(MAX))
select * from TABLE_NEW_XY
DECLARE @Sql NVARCHAR(MAX) = ''
,@TableName VARCHAR(MAX)
,@Id INT

DECLARE Table_Cursor CURSOR FOR 
SELECT 
ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) Id
,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
AND TABLE_NAME LIKE 'Total_%'

OPEN Table_Cursor  
FETCH NEXT FROM Table_Cursor INTO @Id,@TableName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
 IF(@Id = 1)
    BEGIN
        
        SET @Sql = @Sql + 'SELECT count(distinct(email)) email, Profile,Subscriber,origin,country , ''' + RIGHT(@TableName,7) + ''' Date FROM '+@TableName+ 'GROUP BY Profile,Subscriber,origin,country'  ----Modify the columns based on your column names
        SELECT @SQL
    END
 ELSE
    BEGIN
        SET @Sql = @Sql + ' UNION ALL SELECT count(distinct(email)) email, Profile,Subscriber,origin,country,''' + RIGHT(@TableName,7) + ''' Date FROM '+@TableName + 'GROUP BY Profile,Subscriber,origin,country' --Modify the columns based on your column names
    END

 FETCH NEXT FROM Table_Cursor INTO @Id,@TableName
END 

CLOSE Table_Cursor  
DEALLOCATE Table_Cursor

INSERT INTO TABLE_NEW_XY
EXEC (@Sql)

Can anyone help me with this?

>Solution :

A space is needed between @TableName and ‘Group by`.

Instead of @TableName+ 'GROUP BY you need to use @TableName+ ' GROUP BY

DROP TABLE IF EXISTS TABLE_NEW_XY
CREATE TABLE TABLE_NEW_XY (email VARCHAR(MAX),Profile VARCHAR(MAX),Subscriber 
VARCHAR(MAX),origin VARCHAR(MAX),country VARCHAR(MAX),Date VARCHAR(MAX))
select * from TABLE_NEW_XY
DECLARE @Sql NVARCHAR(MAX) = ''
,@TableName VARCHAR(MAX)
,@Id INT

DECLARE Table_Cursor CURSOR FOR 
SELECT 
ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) Id
,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
AND TABLE_NAME LIKE 'Total_%'

OPEN Table_Cursor  
FETCH NEXT FROM Table_Cursor INTO @Id,@TableName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
 IF(@Id = 1)
    BEGIN
        
        SET @Sql = @Sql + 'SELECT count(distinct(email)) email, Profile,Subscriber,origin,country , ''' + RIGHT(@TableName,7) + ''' Date FROM '+@TableName+ ' GROUP BY Profile,Subscriber,origin,country'  ----Modify the columns based on your column names
        SELECT @SQL
    END
 ELSE
    BEGIN
        SET @Sql = @Sql + ' UNION ALL SELECT count(distinct(email)) email, Profile,Subscriber,origin,country,''' + RIGHT(@TableName,7) + ''' Date FROM '+@TableName + ' GROUP BY Profile,Subscriber,origin,country' --Modify the columns based on your column names
    END

 FETCH NEXT FROM Table_Cursor INTO @Id,@TableName
END 

CLOSE Table_Cursor  
DEALLOCATE Table_Cursor

INSERT INTO TABLE_NEW_XY
EXEC (@Sql)