解决“IN”条款限制 [英] Work around 'IN' clause limitation

问题描述

我正在尝试使用 IN 子句从我们的数据库中编写一个宏，除了一个问题。我达到SQL Server的 IN 子句的限制。

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.

我的宏看起来像这样：

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

所以我需要一些如何将查询分割成更小的块，除了传递给SQL查询的参数数量将会随周变化周。

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?

推荐答案

创建一个表函数，将字符串结果返回到可以插入CTE，临时表中或直接在连接中使用的数据集。这是我解决这个限制的最有效方式。以下是Ole Michelsen网站的链接，该网站提供了一个简单而灵活的解决方案。

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.

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

这篇关于解决“IN”条款限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！