'Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'

I try to run 2 dynamic SQL, but I have an error that the syntax is incorrect. When I make a select for every parameter that I declare, there is no problem. But when I try to execute the string I have these erroes. Any help?

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'

--declare Variablen für die Prozedur
declare @DispoColumn nvarchar(max) = (select STRING_AGG(col.name, ', ') within group (order by col.column_id)
                                        FROM  sys.objects obj
                                        JOIN sys.columns col on col.object_id = obj.object_id
                                        JOIN sys.types typ ON col.user_type_id=typ.user_type_id
                                        Where obj.name = @Tabelle and obj.schema_id = 5
                                        GROUP BY obj.name);

                                        
declare @ID nvarchar(150) = (select  MAX(CASE WHEN col.column_id = 1 THEN col.name ELSE NULL END) 
                                FROM  sys.objects obj
                                JOIN sys.columns col on col.object_id = obj.object_id
                                JOIN sys.types typ ON col.user_type_id=typ.user_type_id
                                Where obj.name = @Tabelle and obj.schema_id = 5
                                GROUP BY obj.name);
                                
declare @EFREdispoUpdate nvarchar(max)
declare @ESFdispoUpdate nvarchar(max)
declare @ESFdispoInsert nvarchar(max)
declare @EFREdispoInsert nvarchar(max)
--Update der Tabellen
set @ESFdispoUpdate = 'UPDATE ESF.' + @Tabelle +  ' SET GUELTIG_BIS = GETDATE() WHERE GUELTIG_BIS = ''9999-12-31'' AND ' + @ID + ' IN ( SELECT ' 
                        + @ID + 'FROM ( SELECT ' +@DispoColumn + ' FROM ESF.' + @Tabelle + ' WHERE GUELTIG_BIS = ''9999-12-31''' + ' EXCEPT SELECT ' + @DispoColumn + ' SF.' + @Tabelle + ') as alt );' 
                        + ' UPDATE DISPO.T_TABELLEN SET ANZ_GEANDERT = (SELECT COUNT(*) FROM ESF.'+@Tabelle + ' WHERE GUELTIG_BIS = GETDATE())' 
                        +  'WHERE SCH_NAME = ''ESF'' AND TAB_NAME = ''' +@Tabelle + ''';';
                        
set @EFREdispoUpdate = 'UPDATE EFRE.' + @Tabelle +  ' SET GUELTIG_BIS = GETDATE() WHERE GUELTIG_BIS = ''9999-12-31'' AND ' + @ID + ' IN ( SELECT ' 
                        + @ID + 'FROM ( SELECT ' +@DispoColumn + ' FROM EFRE.' + @Tabelle + ' WHERE GUELTIG_BIS = ''9999-12-31''' + ' EXCEPT SELECT ' + @DispoColumn + ' SF.' + @Tabelle + ') as alt );' 
                        + ' UPDATE DISPO.T_TABELLEN SET ANZ_GEANDERT = (SELECT COUNT(*) FROM EFRE.'+@Tabelle + ' WHERE GUELTIG_BIS = GETDATE())' 
                        +  'WHERE SCH_NAME = ''EFRE'' AND TAB_NAME = ''' +@Tabelle + ''';';
                
EXEC sp_executesql @ESFdispoUpdate;
EXEC sp_executesql @EFREdispoUpdate;
sqlsql-servertsql


Solution 1:[1]

Your issue is because on the second line of your set statements, you forgot a space before FROM

Other minor changes

  • Added brackets to column names just in case column has special character like a space
  • Removed unnecessary GROUP BY clauses in subqueries
  • Changed second subquery from CASE WHEN to WHERE so it's more intuitive
DECLARE @Tabelle NVARCHAR(100) = 'YourTable'

declare @DispoColumn nvarchar(max) = (select STRING_AGG(QUOTENAME(col.name), ', ') within group (order by col.column_id)
                                        FROM  sys.objects obj
                                        JOIN sys.columns col on col.object_id = obj.object_id
                                        WHERE obj.name = @Tabelle AND obj.schema_id = 5
                                        );

                                        
declare @ID nvarchar(150) = (SELECT QUOTENAME(col.name)
                                FROM  sys.objects obj
                                JOIN sys.columns col on col.object_id = obj.object_id
                                Where obj.name = @Tabelle AND obj.schema_id = 5
                                AND col.column_id = 1 
                                );

select @DispoColumn,@ID

declare @EFREdispoUpdate nvarchar(max)
    ,@ESFdispoUpdate nvarchar(max)
    ,@ESFdispoInsert nvarchar(max)
    ,@EFREdispoInsert nvarchar(max)

set @ESFdispoUpdate = 'UPDATE ESF.' + @Tabelle +  ' SET GUELTIG_BIS = GETDATE() WHERE GUELTIG_BIS = ''9999-12-31'' AND ' + @ID + ' IN ( SELECT ' 
                        + @ID + ' FROM ( SELECT ' +@DispoColumn + ' FROM ESF.' + @Tabelle + ' WHERE GUELTIG_BIS = ''9999-12-31''' + ' EXCEPT SELECT ' + @DispoColumn + ' SF.' + @Tabelle + ') as alt );' 
                        + ' UPDATE DISPO.T_TABELLEN SET ANZ_GEANDERT = (SELECT COUNT(*) FROM ESF.'+@Tabelle + ' WHERE GUELTIG_BIS = GETDATE())' 
                        +  'WHERE SCH_NAME = ''ESF'' AND TAB_NAME = ''' +@Tabelle + ''';';
                        
set @EFREdispoUpdate = 'UPDATE EFRE.' + @Tabelle +  ' SET GUELTIG_BIS = GETDATE() WHERE GUELTIG_BIS = ''9999-12-31'' AND ' + @ID + ' IN ( SELECT ' 
                        + @ID + ' FROM ( SELECT ' +@DispoColumn + ' FROM EFRE.' + @Tabelle + ' WHERE GUELTIG_BIS = ''9999-12-31''' + ' EXCEPT SELECT ' + @DispoColumn + ' SF.' + @Tabelle + ') as alt );' 
                        + ' UPDATE DISPO.T_TABELLEN SET ANZ_GEANDERT = (SELECT COUNT(*) FROM EFRE.'+@Tabelle + ' WHERE GUELTIG_BIS = GETDATE())' 
                        +  'WHERE SCH_NAME = ''EFRE'' AND TAB_NAME = ''' +@Tabelle + ''';';

/*Use for debugging*/
SELECT @ESFdispoUpdate
SELECT @EFREdispoUpdate

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephan

Related Questions