Chamando um procedimento armazenado com um comando
Você pode usar um comando para chamar um procedimento armazenado. O exemplo de código no final deste tópico refere-se a um procedimento armazenado no banco de dados de exemplo Northwind, chamado CustOrdersOrders, que é definido da seguinte maneira.
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS
SELECT OrderID, OrderDate, RequiredDate, ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
Consulte a documentação do SQL Server para obter mais informações sobre como definir e chamar procedimentos armazenados.
Esse procedimento armazenado é semelhante ao comando usado em parâmetros de objeto de comando . Ele usa um parâmetro de ID do cliente e retorna informações sobre os pedidos desse cliente. O exemplo de código a seguir usa esse procedimento armazenado como a origem de um conjunto de registros do ADO.
O uso do procedimento armazenado permite que você acesse outra funcionalidade do ADO: a coleção Parameters do método Refresh. Usando esse método, o ADO pode preencher automaticamente todas as informações sobre os parâmetros exigidos pelo comando em tempo de execução. Há uma penalidade de desempenho ao usar essa técnica, pois o ADO deve consultar a fonte de dados para obter as informações sobre os parâmetros.
Existem outras diferenças importantes entre o exemplo de código a seguir e o código em parâmetros de objeto de comando, em que os parâmetros foram inseridos manualmente. Primeiro, esse código não define a propriedade Prepared para True porque é um procedimento armazenado do SQL Server e é pré-compilado por definição. Em segundo lugar, a propriedade CommandType do objeto Command foi alterada para adCmdStoredProc no segundo exemplo para informar ao ADO que se tratava de um procedimento armazenado.
Por fim, no segundo exemplo, o parâmetro deve ser referenciado pelo índice ao definir o valor, pois talvez você não saiba o nome do parâmetro no momento do design. Se você souber o nome do parâmetro, poderá definir a nova propriedade NamedParameters do objeto command como True e fazer referência ao nome da propriedade. Você pode se perguntar por que a posição do primeiro parâmetro mencionado no procedimento armazenado (@CustomerID) é 1 em vez de 0 (objCmd(1) = "ALFKI"). Isso ocorre porque o parâmetro 0 contém um valor retornado do procedimento armazenado do 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