使用VBA和ADO向SQL Server存储过程传递TVP时遇运行时错误3001

可以通过VBA和ADO直接传递SQL Server表值参数，你的代码报错是因为参数类型设置错误，以下是修正方案：

错误原因 #

你使用adVariant作为TVP参数的类型，这不符合ADO传递TVP的要求。正确的做法是使用adDBTypeStructured作为参数类型，并指定参数对应的SQL Server表类型名称。

修正后的完整代码 #

Sub PassTVP()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rsTVP As ADODB.Recordset
    Dim prm As ADODB.Parameter

    ' 创建并打开连接（确保使用支持TVP的Provider：MSOLEDBSQL）
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=MSOLEDBSQL;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;"
    conn.Open

    ' 创建TVP对应的Recordset
    Set rsTVP = New ADODB.Recordset
    With rsTVP
        ' 字段类型需与SQL Server表类型严格匹配
        .Fields.Append "StatusCategoryId", adSmallInt
        .Fields.Append "StatusKeyId", adSmallInt
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic ' 静态游标更适合作为TVP数据源
        .LockType = adLockBatchOptimistic
        .Open

        ' 添加数据
        .AddNew
        .Fields("StatusCategoryId").Value = 1
        .Fields("StatusKeyId").Value = 1
        .Update

        .AddNew
        .Fields("StatusCategoryId").Value = 1
        .Fields("StatusKeyId").Value = 2
        .Update
    End With

    ' 创建命令并配置TVP参数
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = conn
        .CommandText = "dbo.MyProcedureWithTVP"
        .CommandType = adCmdStoredProc

        ' 创建TVP参数：关键修改点
        Set prm = .CreateParameter("@MyTableParam", adDBTypeStructured, adParamInput)
        prm.TypeName = "dbo.MyTableType" ' 指定SQL Server中定义的表类型全名
        prm.Value = rsTVP ' 关联Recordset
        .Parameters.Append prm

        ' 执行存储过程，若需要返回结果可接收Recordset
        Dim rsResult As ADODB.Recordset
        Set rsResult = .Execute
        
        ' 可选：输出结果到Excel工作表
        ' Sheet1.Range("A1").CopyFromRecordset rsResult
        
        rsResult.Close
        Set rsResult = Nothing
    End With

    ' 清理资源
    rsTVP.Close
    cmd.ActiveConnection.Close
    Set rsTVP = Nothing
    Set cmd = Nothing
    Set conn = Nothing
End Sub

关键注意事项 #

• Provider版本：必须使用支持TVP的OLE DB Provider，比如MSOLEDBSQL（不要使用旧版的SQLOLEDB，它不支持表值参数）。
• 类型匹配：Recordset的字段类型必须与SQL Server表类型的字段类型严格对应（比如adSmallInt对应SMALLINT）。
• 参数属性：必须为TVP参数指定TypeName，值为SQL Server中定义的表类型的完整名称（包含架构，如dbo.MyTableType）。
• SQL Server版本：需要SQL Server 2008及以上版本（表值参数是SQL Server 2008引入的特性）。

内容的提问来源于stack exchange，提问作者Eduardo Giorgio