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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwkorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dominic34Author Commented:
wonderful!! it works like a charm!!

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

thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.