title: how to create a dynamic update query with variables?
body: <p>I am trying to create the below dynamic update query with some variables and for some reason, it's not working inside the stored procedure. Can someone suggest to me where I am doing wrong and what's the best practice by avoiding the SQL Injection as well?</p>
  <pre><code>    DECLARE @SQL NVARCHAR(MAX)
      DECLARE @COLUMN1 NVARCHAR(10)
      DECLARE @COLUMN2 NVARCHAR(10)
      DECLARE @TABLENAME NVARCHAR(10)
  
      SET @SQL = 'UPDATE TL
                  SET '+ @COLUMN1 + '= AB.COLUMN1,'
                      + @COLUMN2 + '= AB.COLUMN2
                  FROM' + @TABLENAME + ' TL
                  JOIN ABACUS AB
                  ON TL.REF = AB.REF
                  AND TL.SUBS = AB.SUBS
                  WHERE ' + @COLUMN1 + ' IS NULL
                  AND ' + @COLUMN2 +' IS NULL';
  
      SET @COLUMN1 = (SELECT CONCAT('USER_ID', '8'))
      SET @COLUMN2 = (SELECT CONCAT('USER_ID', '6'))
      SET @TABLENAME = 'POLICYREF';
      EXEC sys.sp_executesql @SQL, @TABLENAME, @COLUMN1, @COLUMN2;
  
      SET @TABLENAME = 'USERREF';
      EXEC sys.sp_executesql @SQL, @TABLENAME, @COLUMN1, @COLUMN2;
  </code></pre>