iterate through a table valued parameter in SQL 2008

Hi,
  I created a user-defined table type in SQL Express 2008 and a StoredProc that will receive the data type from a .net application. The table the SP will receive will contains rows that need to be updated, inserted or, if missing, deleted from the SQL table it refers. So, I need to iterate through the table parameter to know if I have to update, insert or delete the rows in the table. So, how can I do that? with a cursor?

To give you a better understanding, the .net application retrieves an image of the SQL table, put it in a datatable, manipulate the data and send them back to SQL. So, rows can be updated, inserted or deleted.

thanks for your help!
Dominic34Asked:
Who is Participating?
 
dwkorConnect With a Mentor Commented:
You can use MERGE statement
create type tvpParam as table
(
	ID int not null,
	Value varchar(10)
)
go

create table tbl
(
	ID int not null primary key,
	Value varchar(10)
)
go

create proc AProc
(
	@data tvpParam READONLY
)
as
begin
	merge into tbl as Target
	using @Data as Source
	on Target.ID = Source.ID 
	when matched then
		update set 
			Target.Value = Source.Value
	when not matched by Target then
		insert(ID, Value)
		values(Source.Id, Source.Value)
	when not matched by Source then 
		delete;
end

Open in new window

0
 
Dominic34Author Commented:
wonderful!! it works like a charm!!

I didn't knew this instruction! really usefull!!

thanks!!
0
All Courses

From novice to tech pro — start learning today.