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:
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?
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.