title: SQL Server : a strange thing happens with an Update inside a stored procedure
body: <p>I have a stored procedure in SQL Server 2005 that the code inside is something like this :</p>
  
  <pre><code>select Unique_ID as ID
  into tmp_table 
  from table1
  </code></pre>
  
  <p>Then, after some other statements, I do this update :</p>
  
  <pre><code>Update table1 
  set Flag = ‘Y’
  Where Unique_ID in (select Unique_ID from tmp_table)
  </code></pre>
  
  <p>As you notice, I've purposely mistaken <code>ID</code> by <code>Unique_ID</code> (the column name in <code>tmp_table</code>) when I wanted to update.</p>
  
  <p>Now the bizarre thing that happens, is that SQL Server doesn’t show an error when I execute the stored procedure. </p>
  
  <p>It ignores the line where the error is :</p>
  
  <pre><code>Where Unique_ID in (select Unique_ID from tmp_table)
  </code></pre>
  
  <p>And runs just this :</p>
  
  <pre><code>Update table1 set
  Flag = ‘Y’
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p>Thanks a lot.</p>
  
  <p><strong>UPDATE</strong>: </p>
  
  <p>Here is the stored procedure code :</p>
  
  <pre><code>SET QUOTED_IDENTIFIER ON
  SET ANSI_NULLS ON
  GO
  
  CREATE PROCEDURE [dbo].[MyProc]
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @date VARCHAR(20)
  
      SET @date = REPLACE(CONVERT(VARCHAR(8), GETUTCDATE(), 3), '/', '')
              + REPLACE(CONVERT(VARCHAR(8), GETUTCDATE(), 108), ':', '')
  
      DECLARE @PATH AS VARCHAR(500)
      SET @PATH = 'C:\MyPath\' 
  
      BEGIN TRY
              EXEC master.dbo.xp_create_subdir @PATH
      END TRY
      BEGIN CATCH
              PRINT 'FOLDER NOT CREATED'
      END CATCH
  
      IF EXISTS (SELECT name FROM sys.tables WHERE name = 'TMP_TABLE')
         DROP TABLE TMP_TABLE
  
      SELECT  
          UNIQUE_ID [Unique ID] ,
          'col1' COL1,
          'col2' COL2,
          'col3' COL3,
          'col4' COL4
      INTO    
          TMP_TABLE
      FROM    
          TABLE1
      WHERE 
          Flag IN 'N'
  
      --Try to generate file, If there was any problem, return from the SP =&gt; no update
      BEGIN TRY
          DECLARE @QUERY VARCHAR(MAX)
  
          SET @QUERY = 'EXEC master..xp_cmdshell ''sqlcmd -E -s"," -W -h-1 -Q "SET NOCOUNT ON;SELECT * FROM dbo.TMP_TABLE" | findstr /V /C:"-" /B &gt; '
              + @PaTH + '\FileName_' + @date + '.csv'',no_output;'
  
          EXEC(@QUERY)
      END TRY
      BEGIN CATCH
          PRINT 'Exception during file generation'
          RETURN
      END CATCH
  
      PRINT 'File Generated'
  
      UPDATE TABLE1 
      SET Flag = 'Y',
          MODIFICATION_DATE = GETDATE()
      WHERE 
          UNIQUE_ID IN (SELECT UNIQUE_ID FROM TMP_TABLE)
  
      BEGIN TRY
          DROP TABLE dbo.TMP_TABLE
          PRINT 'TMP table dropped'
      END TRY
      BEGIN CATCH
          PRINT 'TMP table not dropped'
      END CATCH
  END
  GO
  </code></pre>
  
  <p>The problem is on this line: </p>
  
  <pre><code>WHERE UNIQUE_ID IN (SELECT **UNIQUE_ID** FROM TMP_TABLE)
  </code></pre>
  
  <p>I should replace <code>UNIQUE_ID</code> with <code>[UNIQUE ID]</code></p>
  
  <p>But SQL Server doesn't throw an error, it simply updates all records.</p>
  
  <p>And here is the structure of table1 :</p>
  
  <pre><code>CREATE TABLE [dbo].[TABLE1]
  (
      UNIQUE_ID int NOT NULL IDENTITY(1, 1), --Primary key
      Flag [varchar] (1) ,
      MODIFICATION_DATE datetime,
  ) 
  </code></pre>