Chiamata di una procedura memorizzata con un comando
È possibile usare un comando per chiamare una procedura memorizzata. L'esempio di codice alla fine di questo argomento fa riferimento a una stored procedure nel database di esempio Northwind, denominato CustOrdersOrders, definito come segue.
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS
SELECT OrderID, OrderDate, RequiredDate, ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
Per altre informazioni su come definire e chiamare stored procedure, vedere la documentazione di SQL Server.
Questa stored procedure è simile al comando utilizzato nei parametri dell'oggetto comando . Accetta un parametro ID cliente e restituisce informazioni sugli ordini del cliente. Nell'esempio di codice seguente, questa stored procedure viene utilizzata come origine per un Recordset ADO .
L'uso della stored procedure consente di accedere a un'altra funzionalità di ADO: la collezione Parameters e il metodo Refresh. Usando questo metodo, ADO può compilare automaticamente tutte le informazioni sui parametri richiesti dal comando in fase di esecuzione. L'uso di questa tecnica comporta una riduzione delle prestazioni, perché ADO deve eseguire una query sull'origine dati per ottenere informazioni sui parametri.
Esistono altre differenze importanti tra l'esempio di codice seguente e il codice in parametri dell'oggetto comando, in cui i parametri sono stati immessi manualmente. In primo luogo, questo codice non imposta la proprietà Prepared su True perché è una stored procedure di SQL Server e viene precompilata per definizione. In secondo luogo, la proprietà CommandType dell'oggetto command è stata modificata in adCmdStoredProc nel secondo esempio per informare ADO che il comando era una stored procedure.
Infine, nel secondo esempio è necessario fare riferimento al parametro in base all'indice quando si imposta il valore, perché è possibile che non si conosca il nome del parametro in fase di progettazione. Se si conosce il nome del parametro, è possibile impostare la nuova proprietà NamedParameters dell'oggetto Command su True e fare riferimento al nome della proprietà. È possibile chiedersi perché la posizione del primo parametro menzionato nella stored procedure (@CustomerID) è 1 anziché 0 (objCmd(1) = "ALFKI"). Questo perché il parametro 0 contiene un valore restituito dalla stored procedure di 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