Synapse SQL is not working as expected while creating Dynamic SQL-CodePudding

I am trying to generate a dynamic SQL.

First I am creating a #TempTable and populating data into the same.

INSERT INTO #TempTable <---#TempTable has two columns viz. ID int IDENTITY(1,1) and 
                            col_name
SELECT COLUMN_NAME AS col_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 
'table_1' AND SCHEMA_NAME = 'dbo')

Then I running a loop as follows

SET @Max_cnt = (SELECT COUNT(*) FROM #TempTable)
SET @Min_cnt = 1
WHILE @Min_cnt < @Max_cnt
set @DySQL_DUP = ' ;WITH Duplicate AS (
            SELECT ID, ROW_NUMBER() OVER(PARTITION BY '  @SourceColNames   ' ORDER BY ' 
    @SourceColNames   ' ) AS ROW_COUNT FROM 
            dbo.table_1 WHERE FileLoadLogID = 1
            UPDATE dbo.table_1 set 
            col_to_update= ''DUPLICATE_ROWS''
             WHERE ID IN (
            SELECT TARGET.ID FROM dbo.table_1 TARGET
            INNER JOIN Duplicate
            ON TARGET.ID = Duplicate.ID
            WHERE Duplicate.ROW_COUNT >1)
SELECT @DySQL_DUP

But this is giving me an error as

'Subquery returned more than 1 value. This is not permitted when the subquery follows =, 
  !=, <, <= , >, >= or when the subquery is used as an expression.'

I am using Synapse SQL. However, The above query is running fine in normal SQL.

Can anyone provide any clue on what I am missing?

CodePudding user response：

you can't pass a string value like that inside dynamic SQL... not even in normal SQL honestly You must put a double single quote around that string value

 {...} col_to_update= ''DUPLICATE_ROWS'' {...}