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

Please read the stack overflow guidelines on asking questions. This question is missing so many context details, it's unanswerable. — Bryan Newman, Jul 05 '18 at 19:21

score 1 · Answer 1 · answered Jul 05 '18 at 19:28

The either col_Id column on tbl_Datos or col_Leyenda in table tbl_Leyenda is declared as a data type NVARCHAR. There is probably data in that column with at least some non-numeric characters in it.

When SQL tries to run your WHERE statment: WHERE col_Id in (@Ids)

It fails to convert the non-numeric data in col_Id to your list of data in @Ids that it is assuming are integers.

You can fix this by putting single quote marks around each Id in your list. It will look more like this:

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

It could also be that the variable @p_Leyenda is being passed in as an integer value. You should attempt to force that to be a string as well. Similarly to your list of col_Ids above.

Thanks for the answer , I have edited the question to focus in the second parameter only since i forced the first parameter as string with added single quote marks and that worked. I will try to figure out how to put the single quote marks in the list of numbers. It Is more a string format issue in the end — Rarm, Jul 05 '18 at 19:59
Tried it with single quote marks and still same thing happens, I do not understand it , finally did it with a string.format that works fine. — Rarm, Jul 08 '18 at 20:10

score 0 · Answer 2 · answered Jul 05 '18 at 19:58

What you need here is a split function.

create a split function as shown here

CREATE FUNCTION [dbo].[SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
            @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
        set @iStart = 2
        insert into @tParts
        values( null )
    end
    else 
        set @iStart = 1
    while 1=1
    begin
        set @iPos = charindex( @cDelimiter, @sString, @iStart )
        if @iPos = 0
            set @iPos = len( @sString )+1
        if @iPos - @iStart > 0          
            insert into @tParts
            values  ( substring( @sString, @iStart, @iPos-@iStart ))
        else
            insert into @tParts
            values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString ) 
            break
    end
    RETURN

END

and then you change your query like below:

UPDATE tbl_Datos 
SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = 'T') 
WHERE col_Id in (Select part from SplitString(@p_Leyenda,','))

score 0 · Accepted Answer · answered Jul 12 '18 at 07:00

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

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

3 Answers3