How to pass List of int value to SP from code behind?

Question

I have a List of int in that i am getting some value now I want to pass all the value to my store procedure one by one or comma separated,

Code Behind:

Cmd.Parameters.AddWithValue("@ImageId", IDList); //IDList is list of int which need to pass in my SP

in SP

@ImageId int //declaration

IF Not Exists(SELECT ImgIns.Id FROM ImgIns WHERE ImgIns.ImageId =@ImageId)

If you would like an alternative to passing a comma seperated list you could also serialise your list to XML and pass in the XML value. — Purplegoldfish, Dec 21 '11 at 10:27

score 1 · Answer 1 · edited May 23 '17 at 10:08

1

Passing them in a comma separated string and split it in t-sql is the best option I found so far.

In Code Behind :

var IDList = String.Join(",", new List<int> {1,2,3});
Cmd.Parameters.AddWithValue("@ImageIds", IDList);

edited May 23 '17 at 10:08

Community

answered Dec 21 '11 at 10:23

VdesmedT

score 0 · Answer 2 · answered Dec 21 '11 at 11:25

Create a sql function to split the list of comma separated int http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 insert this list into some table varibale and then you can wrtie your SP like

DECLARE @ImageIDs TABLE (ImageID INT)

INSERT INTO @ImageIDs select * from dbo.fn_Split(@ImageID)

IF EXISTS (SELECT 1 FROM ImageINstance I INNER JOIN @ImageIDs Img ON I.ImageID = Img.ImageID)

score 0 · Accepted Answer · answered Dec 21 '11 at 11:56

0

For now I am passing the value one by one with the help of for loop and its working fine for me..

 for (int i = 0; i < IDList.Count; i++)
 {
    Cmd.Parameters.Clear();
    Cmd.Parameters.AddWithValue("@ImageId", IDList[i]);
 }

answered Dec 21 '11 at 11:56

Rocky

3 Answers3