Updated query inside EXEC not updating data?

SET @DynamicSQL =   'UPDATE U SET U.ADDRESS1 = U.ADDRESS2, U.ADDRESS2 = NULL FROM USER U INNER JOIN EMPOYEE E ON E.USER_ID = U.USER_ID WHERE U.TYPE = ''' + @TYPE + ''''
EXEC(@DynamicSQL)
PRINT @DynamicSQL

I am creating the dynamic sql inside the stored procedure as shown above, Now when it executes in SP it prints correct sql i wanted, but on rows updated on EXEC are '0'. However when i try to execute the sql which is generated by printing the sql in SP, it shows me 150 rows updated.

Now what could be the reason that EXEC i开发者_开发问答s not able to update the rows, and directly calling PRINTED sql does ?

---

To see the actual row count, you can add SELECT @@ROWCOUNT at the end of your @DynamicSQL. Also you can start a profiler and see what is actually executing. Do you have any triggers on the table?

---

If you execute following statement, does the output show any updated rows?

SET @DynamicSQL = 
  'UPDATE U ' + CHAR(13) + CHAR(10)
  + 'SET    U.ADDRESS1 = U.ADDRESS2 ' + CHAR(13) + CHAR(10)
  + '       , U.ADDRESS2 = NULL ' + CHAR(13) + CHAR(10)
  + 'OUTPUT Inserted.* ' + CHAR(13) + CHAR(10)
  + 'FROM   USER U ' + CHAR(13) + CHAR(10)
  + '       INNER JOIN EMPOYEE E ON E.USER_ID = U.USER_ID ' + CHAR(13) + CHAR(10)
  + 'WHERE  U.TYPE = ' + @TYPE + '''' 
EXEC(@DynamicSQL) 
PRINT @DynamicSQL