Derive parameters of a stored procedure in VBA

Question

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.

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?

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

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

Answer 1

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

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.