getting the Parameters of a stored StoredProcedure

S

steve

hi

Is there someone who can help me how to get the parameters from a stored
procedure using vba?

should i use ADOX or SQLDMO?
 
B

Brendan Reynolds

You don't need ADOX or SQLDMO for that, you can do it with ADO ...

Public Sub GetParams()

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prms As ADODB.Parameters
Dim prm As ADODB.Parameter

Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=(local)"
.Open
End With

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "SalesByCategory"
.CommandType = adCmdStoredProc
.Parameters.Refresh
Set prms = .Parameters
For Each prm In prms
Debug.Print prm.Name
Next prm
.ActiveConnection.Close
End With

End Sub
 
Top