title: Update Statement in a Stored Procedure
body: <p>I have a stored Procedure called Active and the code is:</p>
  
  <pre><code>CREATE PROCEDURE dbo.Active
          (
            @ID INT ,
            @Source VARCHAR(25)
          )
      AS 
          BEGIN
              DECLARE @SQL NVARCHAR(MAX)
              DECLARE @SchemaName SYSNAME
              DECLARE @TableName SYSNAME
              DECLARE @DatabaseName SYSNAME
              DECLARE @BR CHAR(2)
              SET @BR = CHAR(13) + CHAR(10) 
  
  
          SELECT  @SchemaName = Source_Schema ,
                  @TableName = Source_Table ,
                  @DatabaseName = Source_Database
          FROM    Source
          WHERE   ID = @ID
  
  
          SET @SQL = 'UPDATE Source_Table' + @BR
              + 'SET __ACTIVE = CASE WHEN rn = 1 THEN 1 ELSE 0 END' + @BR
              + 'FROM   ( ' + @BR + 'SELECT ROW_NUMBER() OVER (PARTITION BY '
              + @Source + ' ORDER BY __REC_ID DESC) AS rn
          ,  * FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName
              + @BR + ') Source_Table' + @BR          
  
          EXEC @SQL
  
      END 
  </code></pre>
  
  <p>The problem is I am using thsi procedure in another procedure so Everytime that Procedure runs this procedure also runs and does the update to whole table. </p>
  
  <p>The main reason for that update is to check for the duplicates on the table and set the duplicates to 0 and remaining to 1.</p>
  
  <p>I dont want to run this update for whole table but I want the update to run only for Active duplicates.</p>
  
  <p>Is there a way to do it?</p>