sql - Update Statement in a Stored Procedure -

i have stored procedure called active , code is:

create procedure dbo.active         (           @id int ,           @source varchar(25)         )              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            source           id = @id           set @sql = 'update source_table' + @br             + 'set __active = case when rn = 1 1 else 0 end' + @br             + 'from   ( ' + @br + 'select row_number() on (partition '             + @source + ' order __rec_id desc) rn         ,  * ' + @databasename + '.' + @schemaname + '.' + @tablename             + @br + ') source_table' + @br                    exec @sql      end  

the problem using thsi procedure in procedure everytime procedure runs procedure runs , update whole table.

the main reason update check duplicates on table , set duplicates 0 , remaining 1.

i dont want run update whole table want update run active duplicates.

is there way it?

to reiterate issue. calling above stored procedure stored procedure. assume parent procedure determining call "active duplicates". if case, have few options:

1) temp table, have first procedure create global temporary table , use in nested procedure. make sure clean after.

--base procedure creates global temp table proper values; select id  ##activedups duptable somecondition = somecondition  --join global temp table on query set @sql = 'update source_table' + @br     + 'set __active = case when rn = 1 1 else 0 end' + @br     + 'from   ( ' + @br + 'select row_number() on (partition '     + @source + ' order __rec_id desc) rn     ,  * ' + @databasename + '.' + @schemaname + '.' + @tablename     + @br + ') source_table' + @br      + ' inner join ##activedups ad on ad.id = source_table.id'  --drop global temp table drop table ##activedups 

2) parameter, pass comma separated list nested procedure , filter in or exists clause. not scalable. (see added parameter , last line of query)

create procedure dbo.active (     @id int ,     @source varchar(25),     @list varchar(max) )  --...  set @sql = 'update source_table' + @br     + 'set __active = case when rn = 1 1 else 0 end' + @br     + 'from   ( ' + @br + 'select row_number() on (partition '     + @source + ' order __rec_id desc) rn     ,  * ' + @databasename + '.' + @schemaname + '.' + @tablename     + @br + ') source_table' + @br      + ' somecondition in ' @list 

3) add logic dynamic sql fetch proper results. (see last line, appended. cannot determine logic may be.)

set @sql = 'update source_table' + @br     + 'set __active = case when rn = 1 1 else 0 end' + @br     + 'from   ( ' + @br + 'select row_number() on (partition '     + @source + ' order __rec_id desc) rn     ,  * ' + @databasename + '.' + @schemaname + '.' + @tablename     + @br + ') source_table' + @br      + ' somecondition = somecondition'