SQL Passing a List as a SqlCommand Parameter

Question

can i pass a List into a SqlCommand as a paramter?

DELETE FROM MyTable WHERE Key IN @MyParam

command.Parameters.AddValue("MyParam",MyList);

i realize i could just loop over the list but i was looking for a cleaner solution.

agreed it there a process to migrate this to my account on SO? — KellCOMnet, May 17 '10 at 12:42

score 2 · Accepted Answer · answered May 14 '10 at 16:25

2

No, you can't in the current form

There are several options using UDFs or XML or dynamic SQL to pass in lists.

The most comprehensive article on how to do this is "Arrays and Lists in SQL Server 2005 and Beyond" by Erland Sommarskog

answered May 14 '10 at 16:25

gbn

score 0 · Answer 2 · answered Apr 20 '20 at 06:43

Pass them as a table type parameter,

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int )

then consume it in your procedure

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

and do stuff like

delete from dbo.Categories where id in (select CategoryID from @tvpNewCategories) ;

2 Answers2