none
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,

    Aldo.

    Wednesday, July 23, 2008 8:18 AM

Answers

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";  
    sqlComm.Parameters.Add(p); 

     

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

    What I am missing?

    Thanks.

    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)
    AS

    DECLARE @SQL VARCHAR(200)
    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

     

    http://support.microsoft.com/kb/555266/en-us

     

    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
    Moderator
  • Based on your help I worked it out.

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

    http://eggheadcafe.com/community/aspnet/2/10045286/retrieve-data-from-ms-sql.aspx

    Thanks again!
    Aldo.

     

    Thursday, July 24, 2008 1:49 PM