在VBA中派生存储过程的参数

I've tried searching a lot of places and can't quite find what I'm looking for.

我试过找很多地方，但找不到我要找的东西。

I want to write a sub routine in vba that will tell me the parameters of a stored procedure stored on SQL Server.

我想在vba中编写一个子例程，它将告诉我存储在SQL Server上的存储过程的参数。

I know how to execute a stored proc with parameters from excel vba. And I have written a stored proc that takes a stored proc name and returns the parameters. So I could use this. But I thought maybe there is a better way that I don't know about. I found a SQLCommandBuilder Class for VB that would be perfect but I need it in VBA. Is this available in VBA and I just don't know where to activate it?

我知道如何使用excel vba中的参数执行存储的proc。我已经编写了一个存储的proc，它接受存储的proc名称并返回参数。我可以用这个。但我想也许有更好的方法我不知道。我为VB找到了一个SQLCommandBuilder类，这很好，但我需要在VBA中使用它。这在VBA中可用吗?我不知道在哪里激活它?

Thanks

谢谢

**Additional information: After the helpful comments below I am getting closer to what I am aiming to achieve. I want to be able to pass any stored procedure into my subroutine and it will be able to figure out how many parameters it needs and what they will be

**附加信息:在以下有用的评论之后，我正在接近我的目标。我希望能够将任何存储过程传递到我的子例程中，它将能够计算出它需要多少参数以及它们是什么

Here is my code so far

这是我目前的代码

Private Sub execStoredProcedureWithParameters(strServer As String, 

strDatabase As String, strSchema As String, strUSPName As String)

'Declare variables
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset

Dim intParamCount As Integer

'Open database connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb;Data Source=" + strServer + ";Initial Catalog=" + strDatabase + ";Integrated Security=SSPI;"
conn.CommandTimeout = 0

'Here's where the connection is opened.
conn.Open

'This can be very handy to help debug!
'Debug.Print conn.ConnectionString

Set cmd = New ADODB.Command
With cmd
    .CommandText = strSchema + "." + strUSPName
    .CommandType = adCmdStoredProc
    .ActiveConnection = conn
    .Parameters.Refresh

    For intParamCount = 0 To .Parameters.Count - 1
        Debug.Print .Parameters(intParamCount).Name, .Parameters(intParamCount).Type, .Parameters(intParamCounti).Size, .Parameters(intParamCount).Attributes, .Parameters(intParamCount).NumericScale

'        Set prm = cmd.CreateParameter(.Parameters(i).Name, adVarChar, adParamInput, 255)
'        cmd.Parameters.Append prm
'        cmd.Parameters(.Parameters(i).Name).Value = "DBName"
    Next

End With

Set rs = New ADODB.Recordset

'Execute the Stored Procedure
Set rs = cmd.Execute
'Populate the sheet with the data from the recordset
Sheet1.Range("RecordSet").CopyFromRecordset rs

'Cleanup
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

End Sub

Concerning the parameters. Is there a way to convert the DataTypeEnum from the value to the constant. So the type is currently coming through as 202 for the first parameter which I would set to adVarWChar according to this table

有关参数。是否有一种方法可以将数据类型枚举从值转换为常量。所以类型现在作为第一个参数是202，根据这个表我将它设置为adVarWChar

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum

1 个解决方案

#1

You can do this with ADODB, add a reference to Microsoft ActiveX Data Objects then you can:

你可以使用ADODB，添加一个对Microsoft ActiveX数据对象的引用，然后你可以:

With New ADODB.Command
    Set .ActiveConnection = myAdoDbConnection
    .CommandText = "[dbo].[usp_XXX]"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh

    For i = 0 To .Parameters.Count - 1
        Debug.Print .Parameters(i).Name, .Parameters(i).Type, .Parameters(i).Direction
    Next
End With

There should be a necessity requiring this as it requires a round trip to the server.

应该有必要这样做，因为它需要往返到服务器。

#1