Using 'Table-Valued Parameters' to pass a set of IDs?

Question

Hi,

I want to pass a set of row's IDs to use it in updating data, I want to use 'Table-Valued Parameters' to pass 'myDataTable' which has only one column (id ->'int'), I created the TableType, SP and the C# code to pass the dataTable to SP.

But i couldnt know how to use each 'ID' in @IDsDataTypeds, for example:

ALTER PROCEDURE UpdateNamesByIDs
	@IDsDataType AS dbo.[NamesDataType2] readonly
AS
BEGIN
	
	SET NOCOUNT ON;
	
	WHILE (SELECT id FROM @IDsDataType) < 10
		BEGIN
			UPDATE Names
			SET Name = N'noname'
			WHERE id = (SELECT id FROM @IDsDataType)
		END
END

How to do this?

Thanks..

Answer 1

Your question is not clear. You don't need WHILE in your procedure, simply use

UPDATE Names SET Name = N'noname' where exists (select 1 from @IDsDataType I where I.Id = Names.ID and I.Id < 10)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Use a set based operations, i.e. something like this

BEGIN

UPDATE Names
SET Name = N'noname'
WHERE id IN  (SELECT T.id FROM @IDsDataType AS T)

END

Using WHILE will require cursor in the SP and it could be real performance problem in TSQL.

Answer 3

http://www.codeproject.com/Articles/37174/Table-Value-Parameter-in-SQL-Server-2008

http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.100%29.aspx

http://msdn.microsoft.com/en-us/library/bb510489%28v=sql.100%29.aspx

Answer 4

Thanks Naomi, your answer solve my problem.

about the question i thought as you thought its not clear!

in my code i need to update a set of rows using its IDs, but i dont want to use a loop in my program and make a multiple connection to sql server, i want to pass the IDs in one connection.

I hope its more clear now!

Thanks again.

Answer 5

Have you been able to solve the problem now? What is your client application (which language)?

Take a look at this link

SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven!

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Thanks Naomi, but how can I use each 'ID' as a separate variable for example in C#:

foreach(int x in myList)

{

    //Some code using variable 'x'

}

I want to do something like this in SQL because I will use each 'ID' in a several queries in the same SP..

Answer 7

Well, T-SQL is best used in set-based operations, however, you can use a WHILE loop or cursor if you need row by row processing.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Any idea how to use WHILE loop whith @IDsDataType for row by row processing?

Answer 9

declare @Id int

select @Id = min(id) from @IDsDataType

while exists (select 1 from @IdsDataType where Id >= @Id)

  begin

     do something

     select top (1) @Id = Id from @IDsDataType where Id >=@Id order by Id

  end

---------

Something like this.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 10

Your question is anything but clear. However, this article on my web site include several examples of how to use table-valued variables from C# and in SQL Server:

http://www.sommarskog.se/arrays-in-sql-2008.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]