最近对 pandas, Power Query 和 SQL 的数据处理和加工进行了一些类似的处理，希望通过比较看到各种处理方式的优点。之前笔者用的比较多的是 VBA + ADO，VBA + ADO 的方式其实离不开 Sql 语句，这种方式也有非常方便：前台有 Excel，后台有数据库就能实现。
由于 VBA 对多行字符串的支持不好，代码中表达 sql 语句极不方便，所利用数据库的存储过程，将sql 语句放在存储过程中。这样，代码中只关心存储过程的名称和参数，代码大大简化，并且利用了数据库的计算能力，性能得到提高。本文介绍在 VBA 中利用 ADO 如何操作存储过程的要点。
ADO 连接到数据库，需要连接字符串。在 Windows 上连接到数据库，可以利用数据库连接向导，帮助构建连接字符串，不用死记。方法如下：在任意位置新建一个扩展名为 udl 的文件，然后双击 udl 文件，启动数据库连接向导。假设需要连接到 SQL Server 数据库，首先选择数据库驱动：
点击下一步按钮，输入数据库服务器名称和登录的方式：
点击测试连接按钮，测试是否连接成功。没有问题，点击确定按钮完成。然后用可以打开文本文件的编辑器打开 udl 文件，文件保存的就是我们需要的连接字符串。
[oledb] ; Everything after this line is an OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=stonetest; Data Source=STONEWM-PC\SQLEXPRESS本例设计的场景是在存储过程中计算物料在某个月份的进出存数据。sql 语句可以用子查询或者 Common Table Expression。完成后的存储过程代码如下：
CREATE PROCEDURE [dbo].[spGetStockBalances] @y int, @m int AS BEGIN WITH merged AS ( SELECT d.DocNo, d.MaterialNo, d.Qty, h.MovementType, h.DocDate, t.InOutSign, YEAR(h.DocDate) AS TxYear, MONTH(h.DocDate) AS TxMonth, CASE InOutSign WHEN '+' THEN ISNULL(d.Qty, 0) else -1 * ISNULL(d.Qty, 0) END AS ActualQty FROM stock_movement_details d INNER JOIN stock_movement_headers h ON d.DocNo = h.DocNo LEFT OUTER JOIN movement_types t ON h.MovementType = t.MovementTypeID ), calculated AS ( SELECT MaterialNo, InOutSign, TxYear, TxMonth, ActualQty, CASE WHEN TxYear < @y OR ( TxYear = @y AND TxMonth < @m ) THEN ActualQty ELSE 0 END AS BeginQty, CASE WHEN TxYear = @y AND TxMonth = @m AND InOutSign = '+' THEN ActualQty ELSE 0 END AS StockIn, CASE WHEN TxYear = @y AND TxMonth = @m AND InOutSign = '-' THEN ActualQty ELSE 0 END AS StockOut FROM merged ) SELECT MaterialNo, SUM(BeginQty) AS BeginQty, SUM(StockIn) AS StockIn, SUM(StockOut) AS StockOut, SUM(BeginQty+StockIn+StockOut) AS EndQty FROM calculated GROUP BY MaterialNo; END;存储过程中有两个变量 @y 表示年度，@m 表示月份。
在 VBE 环境中，添加对 Microsoft ActiveX Data Objects 对象的引用，然后编写如下代码：
Option Explicit Public Sub GetStockBalance() Dim conn As New ADODB.Connection Dim cmd As New ADODB.Command conn.ConnectionString = "Provider=SQLOLEDB.1;" & _ "Integrated Security=SSPI;Persist Security Info=False;" & _ "Initial Catalog=stonetest;Data Source=STONEWM-PC\SQLEXPRESS" conn.Open With cmd .ActiveConnection = conn .CommandType = adCmdStoredProc .CommandText = "dbo.spGetStockBalances" .Parameters.Append .CreateParameter("@y", adInteger, adParamInput, , 2008) .Parameters.Append .CreateParameter("@m", adInteger, adParamInput, , 4) End With Dim rs As New ADODB.Recordset Set rs = cmd.Execute ' headers Dim col As Integer Dim startCell As Range col = 0 Set startCell = Sheet1.Range("A1") For col = 0 To rs.Fields.Count - 1 startCell.Offset(0, col).Value = rs.Fields(col).Name Next ' lines startCell.Offset(1, 0).CopyFromRecordset rs rs.Close conn.Close Set rs = Nothing Set cmd = Nothing Set conn = Nothing End SubADO 连接 MySQL 数据库，使用 ODBC 数据源。在 Windows 的 Run 窗口中输入 odbcad32 命令，打开配置连接界面。确保已经下载和安装 ODBC for MySQL 驱动程序：
然后在界面中配置数据源：
MySQL 的存储过程与 T-SQL 语法有一些区别，包括不支持 CTE，所以需要用子查询来解决嵌套调用。
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spStockBalances`( y int, m int ) BEGIN SELECT MaterialNo, SUM(BeginQty) AS BeginQty, SUM(StockIn) AS StockIn, SUM(StockOut) AS StockOut, SUM(BeginQty+StockIn+StockOut) AS EndQty FROM ( SELECT MaterialNo, TxYear, TxMonth, InOutSign, ActualQty, CASE WHEN (TxYear < y) OR ( TxYear = y AND TxMonth < m ) THEN ActualQty ELSE 0 END AS BeginQty, CASE WHEN TxYear = y AND TxMonth = m AND InOutSign = '+' THEN ActualQty ELSE 0 END AS StockIn, CASE WHEN TxYear = y AND TxMonth = m AND InOutSign = '-' THEN ActualQty ELSE 0 END AS StockOut FROM ( SELECT d.MaterialNo, d.Qty, h.MovementType, h.DocDate, t.InOutSign, YEAR(h.DocDate) AS TxYear, MONTH(h.DocDate) AS TxMonth, CASE WHEN t.InOutSign = '+' THEN ifnull(d.Qty, 0) ELSE -1 * ifnull(d.Qty,0) END AS ActualQty FROM stock_movement_details AS d INNER JOIN stock_movement_headers AS h ON d.DocNo = h.DocNo LEFT JOIN movement_types AS t ON h.MovementType = t.MovementTypeID ) AS joined ) AS calculated GROUP BY MaterialNo ORDER BY MaterialNo; END$$ DELIMITER ;根据测试，用 Command 命令创建参数并调用存储过程的结果不正确，所以直接用 Recordset Open 方法能返回正确的结果。
Option Explicit Public Sub GetStockBalance() Dim conn As New ADODB.Connection Dim connStr As String connStr = "DRIVER={MySQL ODBC 8.0 Unicode Driver}; " & _ "SERVER=localhost; PORT=3306; DATABASE=stonetest; USER=root; PASSWORD=pwd;OPTIONS=3" conn.ConnectionString = connStr conn.Open Dim rs As New ADODB.Recordset rs.Open "call spStockBalances(2008,4)", conn, adOpenStatic, adLockReadOnly ' headers Dim col As Integer Dim startCell As Range col = 0 Set startCell = Sheet1.Range("A1") For col = 0 To rs.Fields.Count - 1 startCell.Offset(0, col).Value = rs.Fields(col).Name Next ' lines startCell.Offset(1, 0).CopyFromRecordset rs ' clean up rs.Close conn.Close Set rs = Nothing Set conn = Nothing End Sub