Updated query inside EXEC not updating data ?

Question

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 is not able to update the rows, and directly calling PRINTED sql does ?

Answer 1

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 

Answer 2

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?