0

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:

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
AdminTech
  • 49
  • 1
  • 10
  • I haven't experienced this personally, but I'd check here: http://stackoverflow.com/q/1069415/1316573 – Daniel Nov 09 '15 at 21:54
  • Can you just `union` two (or more) queries, each of which stays below the limit for an `IN` clause in SQL Server. (the limit in Oracle is 1000; I'm not sure what it is for SQL Server) – Marc Nov 09 '15 at 21:57
  • 1
    Can't you just write it as two separate IN clauses with an OR? EG: WHERE (item in ('A','B',....) OR item in ('X','Y',....)) AND ... – Kris B Nov 09 '15 at 22:02
  • @KrisB's approach is what I'd typically use (with Oracle but I'm guessing would also work here). Until you hit the limit on the length of a SQL string. – Tim Williams Nov 09 '15 at 22:38

1 Answers1

2

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

Mike Zalansky
  • 796
  • 7
  • 14