Use string of numbers as SqlParameter to SQL query resource with IN Operator

Question

I am trying to pass a string p_strIds with number values separated by ",":

2844099,2844100,2844101,2844102,2844103,2844104,2844105,2844106,2844107,2844108,2844109,2844110,2844111,2844112,2844113,2844114,2844115,2844116,2844117,2844118

The string is used as a SqlParameter:

mySqlCommand.Parameters.Add(new SqlParameter("@p_Ids", p_strValores));

to be used by the following resource (added as resource) query in the IN Operator:

UPDATE tbl_Datos 
SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') 
WHERE col_Id in (@p_Ids)

The query and the IN Operator should end up like this:

UPDATE tbl_Datos 
SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') 
WHERE col_Id in (2844099,2844100,2844101,2844102,2844103,2844104,2844105,2844106,2844107,2844108,2844109,2844110,2844111,2844112,2844113,2844114,2844115,2844116,2844117,2844118)

But it says it cannot convert nvarchar to int, how can I format parameter to be used at IN(...Ids...)

It works if I use the parameter p_strIds with string.Format(queryString, p_strIds) like this:

queryString = UPDATE tbl_Datos SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') WHERE col_Id in ({0})
strSql = string.Format(queryString, p_strValores)
mySqlCommand = new SqlCommand(strSql, m_obSqlConnection);

any ideas about how to do it in the first approach with the sql statement as a resource?

Thanks

Answer 1

Finally , the only way is to convert string to List:

List<string> p_Ids= p_strValores.Split(',').ToList();

Then convert each value in the List to int and add them to an aux List e.g. aux_p_Ids, then use it in the sql query:

UPDATE tbl_Datos SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = @p_Leyenda) WHERE col_Id in aux_p_Ids