Вызов хранимой процедуры с помощью команды
Для вызова хранимой процедуры можно использовать команду. В примере кода, приведённом в конце этого раздела, ссылаются на хранимую процедуру в образце базы данных Northwind, с именем CustOrdersOrders, описанную следующим образом.
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS
SELECT OrderID, OrderDate, RequiredDate, ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
Дополнительные сведения об определении и вызове хранимых процедур см. в документации по SQL Server.
Эта хранимая процедура аналогична команде, используемой в параметрах объекта команд . Он принимает параметр идентификатора клиента и возвращает сведения о заказах этого клиента. В следующем примере кода хранимая процедура используется в качестве источника для набора записей ADO .
Используя хранимую процедуру, вы можете получить доступ к другой возможности ADO: метод Parameters коллекции Refresh. С помощью этого метода ADO может автоматически заполнить все сведения о параметрах, необходимых команде во время выполнения. Существует штраф за производительность при использовании этого метода, так как ADO должен запрашивать источник данных для получения сведений о параметрах.
Другие важные различия существуют между приведенным ниже примером кода и кодом в параметрах объекта команд, где параметры были введены вручную. Во-первых, этот код не устанавливает свойство Prepared в True, так как это хранимая процедура SQL Server, которая компилируется заранее. Во-вторых, свойство CommandType объекта Command было изменено на adCmdStoredProc во втором примере, чтобы сообщить ADO, что команда была хранимой процедурой.
Наконец, во втором примере параметр должен ссылаться на индекс при задании значения, так как имя параметра может не быть известно во время разработки. Если вы знаете имя параметра, можно задать новое свойство NamedParameters объекта Command значение True и указать имя свойства. Вы можете задаться вопросом, почему позиция первого параметра, упоминаемого в хранимой процедуре (@CustomerID), составляет 1 вместо 0 (objCmd(1) = "ALFKI"). Это связано с тем, что параметр 0 содержит возвращаемое значение из хранимой процедуры SQL Server.
'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