Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    45

    Unanswered: Where In () values generated dynamically

    I need to design a query using where in () to select from a list of multiple values...

    SELECT EmployeeID, EmployeeName from EMPLOYEES WHERE OFFICE_ID in (1,5,7)

    simple enough.

    However, the list of OFFICE_IDs in the where clause needs to be generated dynamically in my asp script.

    Can't seem to get this to work using Createparameter. The OFFICE_ID values are int. How can I create a parameter of multiple int values?

    Any help would be appreciated.

  2. #2
    Join Date
    Sep 2009
    Posts
    44
    Code:
    The OFFICE_ID values are int. How can I create a parameter of multiple int values?
    I'm guessing you have an array of values.

    I'd strongly recommend scanning it to be sure they're actually all integers. This code isn't VBScript but regular VB as I'm not an ASP guy. If you're using ASP.NET (it does help to mention what language you're using) just make sure you type your arrays.

    Code:
        Dim a(1 To 3)
        a(1) = 5
        a(2) = 89
        a(3) = "error"
        For Each i In a
            x = CInt(i) ' You don't have to do anything with x
        Next
        If UBound(a) - LBound(a) < 1 Then
            inexpr = "1 = 1"    ' Handle empty arrays correctly
        Else
            inexpr = "IN (" + Join(a, ", ") + ")"
        End If
    That code will fail when it tries to CInt the string. I'd recommend always checking this stuff before sending it to the DBMS. If the check is taking up too much time, you'll see that when you profile and you can easily remove it, but this is no different than the checks CreateParameter has to do.

    To use it, you insert the value of inexpr in your SQL. It is, theoretically, possible to use parameters, but that's just another means of validating your inputs.

  3. #3
    Join Date
    Jul 2004
    Posts
    45
    The values are coming from an array and the values are int, that is verified earlier in the code.

    I know I could dynamically generate the SQL statement, just wondering if there was a way to do it through createparameter.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What database are you using?
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2004
    Posts
    45
    SQL Server 2008

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fab-u-lous!!

    Take a look at table valued parameters: SQL Server 2008: Table Valued Parameters - SQLTeam.com
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    You can use array by constructing Request.Querystrings

    WHERE OriginalsQuery.[Orig Old Stock] in ("& Request.Querystring("Stock")&")

    String and Numeric values are only comma separated (no quotes)

    Simon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •