Jet 引擎 (Access)：将值列表传递给存储过程

Question

我目前正在编写一个基于 VBA 的 Excel 加载项，该加载项在很大程度上基于 Jet 数据库后端（我使用 Office 2003 套件 - 无论如何，问题与更新版本的 Office 相同）。

在我的应用程序初始化期间，我创建了在文本文件中定义的存储过程。这些过程由我的应用程序在需要时调用。

让我举一个简单的例子来描述我的问题：假设我的应用程序允许最终用户选择他们想要详细信息的订单标识符。这是表定义：

表tblOrders：OrderID LONG、OrderDate DATE、（其他字段）

最终用户可以选择一个或多个 OrderID，显示在表单中 - 他/她只需勾选她/他想要的相关 OrderID 的复选框详细信息（订单日期等）。

因为我事先不知道他/她会选择多少个 OrderID，所以我可以通过基于表单上所做的选择级联 WHERE 子句，在 VBA 代码中动态创建 SQL 查询：

SELECT * FROM tblOrders WHERE OrderID = 1 OR OrderID = 2 OR OrderID = 3

或者更简单，通过使用 IN关键字：

SELECT * FROM tblOrders WHERE OrderID IN (1,2,3)

现在，如果我将这个简单的查询变成一个存储过程，以便我可以动态传递我想要显示的 OrderID 列表，我该怎么办？我已经尝试过类似的事情：

CREATE PROCEDURE spTest (@OrderList varchar) AS
  SELECT * FROM tblOrders WHERE OrderID IN (@OrderList)

但这不起作用（我期待如此），因为 @OrderList 被解释为字符串（例如“1,2,3”）而不是长列表价值观。 （我改编自此处的代码：将列表/数组传递给 SQL Server 存储过程）

我'我希望尽可能避免通过纯 VBA 代码（即动态地将值列表分配给我的应用程序中硬编码的查询）来处理此问题。如果这是不可能的，我会理解。

有什么线索吗？

Answer 1

您可以动态创建查询语句字符串。在 SQL Server 中，您可以拥有一个返回值为 TABLE 的函数，并像调用表一样内联调用该函数。或者在 JET 中，您还可以创建一个 kludge - 一个临时表（或提供临时表功能的持久表），其中包含列表中的值，每行一个，并连接到该表。因此，查询过程分为两步：1) 使用 INLIST 值填充临时表，然后 2) 在临时表上执行查询连接。

       MYTEMPTABLE
       autoincrementing id
       QueryID  [some value to identify the current query, perhaps a GUID]
       myvalue  one of the values in your in-list, string


      select * from foo
      inner join MYTEMPTABLE on foo.column = MYTEMPTABLE.myvalue and MYTEMPTABLE.QueryId = ?

      [cannot recall if JET allows ANDs in INNER JOIN as SQL Server does -- 
      if not, adjust syntax accordingly]

而不是通过

      select * from foo where foo.column IN (... )       

这种方式，您可以让同一个表同时处理多个查询，因为每个查询都有一个唯一的标识符。您可以在完成后删除列表中的行：

      DELETE FROM MYTEMPTABLE where QueryID = ?

PS 有多种方法可以处理连接的数据类型问题。您可以根据需要在 MYTEMPTABLE 中转换字符串值，或者可以在 MYTEMPTABLE 中拥有不同数据类型的多个列，插入并连接到正确的列：

       MYTEMPTABLE
       id
       queryid
       mytextvalue
       myintvalue
       mymoneyvalue
       etc