Work around 'IN' clause limitation

I am trying to write a macro to query from our database using the IN clause except with one problem. I am reaching the limit of the IN clause for SQL Server.

My macro looks like this:

  • Find user who have use same IP
  • Zeppelin SQL: reuse data of query without another interpreter or a new query
  • Group by Time Interval
  • SQL Server : when to use collation and nvarchar
  • ADO.NET Entity Framework and identity columns
  • SQL Server 2005 / 2008 - multiple filegroups?
  • Dim row_count As Double
    row_count = ActiveSheet.UsedRange.Rows.Count - 1
    half_row_count = row_count
    Dim i As Double
    Dim products As String
    For i = 2 To half_row_count
        Dim product_id As String
        product_id = Cells(i, 1).Value
        'test = sixtyDays(product_id, conn)
        'Cells(i, 10).Value = test
        products = products & "'" & product_id & "'" & ", "
    Next i
    Dim sample As New ADODB.Recordset
    products = Left(products, Len(products) - 2)
    Set sample = sixtyDays(products, conn)
    Sheets(1).Range("K2").CopyFromRecordset sample
    conn.Close
    
    Function sixtyDays(ProductID As String, new_conn As ADODB.Connection) As ADODB.Recordset
        Dim sConnString As String
        Dim rst As New ADODB.Recordset
        Dim recordsAffecfted As Long
        StrQuery = "SELECT ProductAnalysisByMonth.SalesQty FROM ProductAnalysisByMonth WHERE ProductAnalysisByMonth.ProductID IN (" + ProductID + ") AND ProductAnalysisByMonth.Month = " + CStr(Month(Date) - 2)
        rst.Open StrQuery, new_conn
        Set sixtyDays = rst
    End Function
    

    So I need to some how split the query into smaller chunks, except, the number of arguments passed to the SQL query will vary from week to week.

    What is the most efficient way of handling this problem?

  • Programatically Determine Master Server in SQL Failover Scenario
  • Is there an overview of all SQL Server 2012 error codes?
  • Partitioned view execution plan
  • Ways to avoid eager spool operations on SQL Server
  • Best practice for SQL Server 2008 schema change
  • How to retrieve binary image from database using C# in ASP.NET
  • One Solution collect form web for “Work around 'IN' clause limitation”

    Create a table function that will return your string results into a data-set that can be inserted into a CTE, temp table, or used directly in a join. This has been the most effective way for me to get around this limitation. Below is a link to Ole Michelsen’s website who provides a simple but flexible solution.

    Link: http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.