sql server 2008 - Loop items in list of user defined table types -

i have simple table 2 columns carid int, primary key , carname, varchar.

i need create stored procedure accepts list of cars. if car carid doesn't exist, want insert car, , if exists, want update it.

i created user-defined table type cartype:

create type dbo.cartype table (     carid int null,     carname varchar(800) not null, ); 

and stored procedure insertcars:

create procedure dbo.insertcars     @cars cartype readonly     declare @carcount int = 0;     declare @counter int = 0; begin     set @carscount = (select count(carid) @cars);      while(@counter < @carscount)             begin try             --how item list cars?             @currentcar = cars(@counter)              if exists(select 1 cars carsid = currentcar.carid)                 --if doesn’t exist insert                 begin                     insert cars(carid, carname)                     select * @currentcar;                 end               else                 begin                     --if exist update                 end              end              set @counter= @counter + 1;          end try          begin  catch             print (error_message());         end catch end 

i don't know how current car in loop list of cars (parameter cars in stored procedure).

or elegant solution problem.

it seems may rid of loop here:

create procedure dbo.insertcars     @cars cartype readonly begin     set nocount on;      update c     set c.carname = c2.carname     cars c         join @cars c2 on c2.carid = c.carid;      insert cars(carid, carname)     select c.carid, c.carname     @cars c     not exists (select 1 cars carid = c.carid);  end 

or (using merge construct):

create procedure dbo.insertcars     @cars cartype readonly begin     set nocount on;      merge cars target     using (select carid, carname @cars) source (carid, carname)     on (target.carid = source.carid)     when matched          update set carname = source.carname     when not matched            insert (carid, carname)         values (source.carid, source.carname);  end