Aufrufen einer gespeicherten Prozedur mit einem Befehl
Sie können einen Befehl verwenden, um eine gespeicherte Prozedur aufzurufen. Das Codebeispiel am Ende dieses Themas bezieht sich auf eine gespeicherte Prozedur in der Northwind-Beispieldatenbank namens "CustOrdersOrders", die wie folgt definiert ist.
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS
SELECT OrderID, OrderDate, RequiredDate, ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
Weitere Informationen zum Definieren und Aufrufen gespeicherter Prozeduren finden Sie in der SQL Server-Dokumentation.
Diese gespeicherte Prozedur ähnelt dem Befehl, der in Command Object Parametersverwendet wird. Er verwendet einen Parameter für die Kunden-ID und gibt Informationen zu den Bestellungen dieses Kunden zurück. Im folgenden Codebeispiel wird diese gespeicherte Prozedur als Quelle für ein ADO-Recordset-verwendet.
Mithilfe der gespeicherten Prozedur können Sie auf eine andere Funktion von ADO zugreifen: die Parameters-Auflistung Refresh-Methode. Mithilfe dieser Methode kann ADO automatisch alle Informationen zu den Parametern ausfüllen, die vom Befehl zur Laufzeit benötigt werden. Es gibt eine Leistungseinbuße bei der Verwendung dieser Technik, da ADO die Datenquelle für die Informationen zu den Parametern abfragen muss.
Weitere wichtige Unterschiede bestehen zwischen dem folgenden Codebeispiel und dem Code in Command Object Parameters, wobei die Parameter manuell eingegeben wurden. Zunächst legt dieser Code die Prepared-Eigenschaft nicht auf True fest, da es sich um eine gespeicherte SQL Server-Prozedur handelt und per Definition vorkompiliert ist. Zweitens wurde die CommandType-eigenschaft des Command-Objekts in adCmdStoredProc im zweiten Beispiel geändert, um ADO darüber zu informieren, dass der Befehl eine gespeicherte Prozedur war.
Schließlich muss im zweiten Beispiel beim Festlegen des Werts auf den Parameter durch Index verwiesen werden, da Sie möglicherweise den Namen des Parameters zur Entwurfszeit nicht kennen. Wenn Sie den Namen des Parameters kennen, können Sie die neue NamedParameters-eigenschaft des Command-Objekts auf "True" festlegen und auf den Namen der Eigenschaft verweisen. Sie fragen sich vielleicht, warum die Position des ersten in der gespeicherten Prozedur erwähnten Parameters (@CustomerID) 1 anstelle von 0 (objCmd(1) = "ALFKI") ist. Dies liegt daran, dass Parameter 0 einen Rückgabewert aus der gespeicherten SQL Server-Prozedur enthält.
'BeginAutoParamCmd
On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset
' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "CustOrdersOrders"
objCmd.CommandType = adCmdStoredProc
' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn
' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh
' Set the param value.
objCmd(1) = "ALFKI"
' Execute once and display...
Set objRs = objCmd.Execute
Debug.Print objParm1.Value
Do While Not objRs.EOF
Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2) & vbTab & objRs(3)
objRs.MoveNext
Loop
' ...then set new param value, re-execute command, and display.
objCmd(1) = "CACTU"
Set objRs = objCmd.Execute
Debug.Print objParm1.Value
Do While Not objRs.EOF
Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2) & vbTab & objRs(3)
objRs.MoveNext
Loop
'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set objParm1 = Nothing
Exit Sub
ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If
If objConn.State = adStateOpen Then
objConn.Close
End If
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set objParm1 = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndAutoParamCmd
'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
sCnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _
"Integrated Security='SSPI';Initial Catalog='Northwind';"
oCn.Open sCnStr
If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
End If
End Function
'EndNewConnection