c# - Update or Insert records in database based on result set -

i've table this

i've table sections in there 6 sections id 1 6. list of section id user gives information current active section of user. suppose list returned me has section ids follows {2,3,4,5} user id 1. question

1. how can pass list of section ids stored procedure
2. i want update active flag of record section id 1 , user id 1 since list of section ids doesn't have entry of 1
3. i want insert new record of section id 5 user id 1 in same table returned in list of section ids.

can please tell me how achieve this?

i can total section id's following query

select id sections 

but don't know iterate between total list of section id's , compare list of section ids returned c#

to answer complete question.

1. said in comment: table valued parameters

first create udtt store sections ids input stored procedure.

create type [dbo].[sectionidudtt] table(     [id] int not null ) 

use udtt parameter stored procedure:

alter procedure [dbo].[yourspname] @sectionid sectionidudtt readonly,     @userid int begin 

in order call stored procedure must first fill sectionidudtt before call stored procedure.

declare @sectionid sectionidudtt;  insert @sectionid  --your values here  exec yourspname @sectionid, @userid; 

take @ datatable class in order call c#. make sure user has execute permissions on udtt.

2 in stored procedure

set records inactive of user existed not in table valued parameter.

update yourtable set isactive = 0 userid = @userid     , sectionid not in (select id @sectionid)     , sectionid in (select id sections) --not needed 

3 in stored procedure

just insert record not yet exists. assume id identity column.

insert yourtable (userid, sectionid, isactive) select @userid,     s.id,     1 @sectionid s not exists (     select 1     yourtable y     y.sectionid = s.id         , y.userid = @userid     ) 

i recommend transaction management in stored procedure.