How to pass a parameterized variable with several int values to an sql statement using "In ('value1', 'value2')" RRS feed

  • Question

  • Hi,


    I am using C# code to run an MS SQL Server 2000 query.

    The complete query is too long, so I will copy here only the problematic line.


    The original line, I am using in the sql string, is:

    Code Snippet
    // + @" AND Stock.DOCUMENTID In ('1', '4', '5', '11', '37','78', '95') "

    and it works okay.


    Now, I need to use a parameter with that line (instead of the values), so I am changing it to:

    Code Snippet
    @" AND Stock.DOCUMENTID In (@stockDocumentIdIn) "

    where I declare the variable @stockDocumentIdIn as:

    Code Snippet

    string stockDocumentIdIn = "'1', '4', '5', '11', '37','78', '95'";

    sqlComm.Parameters.AddWithValue("@stockDocumentIdIn", stockDocumentIdIn);

    and I get this error: Syntax error converting the nvarchar value '1', '4', '5', '11', '37','78', '95'  to a column of data type int


    I tried using just

    Code Snippet
     string stockDocumentIdIn = "1";

    and it works, but when trying to add more values, like

    Code Snippet
    string stockDocumentIdIn = "1, 4, 5";

    I get error again.


    How can I work it around?


    Thanks in advance,


    Wednesday, July 23, 2008 8:18 AM


All replies

  • I am trying the following, but still getting error Syntax error converting the varchar value   '0, 2'  to a column of data type smallInt.

    SELECT...  FROM ... WHERE ... AND Stock.STATUS Not In (@A)  


    The declaration of the variable @A  in the method is as follows:

    SqlParameter p = new SqlParameter();  
    p.ParameterName = "@A";  
    p.SqlDbType = SqlDbType.VarChar;  
    p.Value = "0, 2";  


    How do I catch this parameter as varchar or clob type in the SQL statement?

    What I am missing?


    Wednesday, July 23, 2008 10:43 AM
  • Are you calling a stored procedure? The only way to use IN or NOT IN with multiple values passed as a single parameter is to use a stored procedure and pass in the single value as a string and execute the statement as dynamic SQL. See example below:

    CREATE [dbo].[getStock]
    @IdString VARCHAR(100)

    SET @SQL = 'SELECT ... FROM ... WHERE ... AND Stock.STATUS NOT IN (' + @IdString + ')'

    EXEC (@SQL)

    Wednesday, July 23, 2008 11:59 AM
  • With SQL Server you could do this using XML structure. See my KB article about this



    Using dynamic strings could be dangerous and potentially introduce SQL injection vulnerability, so you should be careful and verify input.

    Wednesday, July 23, 2008 4:48 PM
  • Based on your help I worked it out.

    The answer is too long to post it here, so there is a link:

    Thanks again!


    Thursday, July 24, 2008 1:49 PM