Chiamata di una stored procedure con Command
È possibile usare un comando per chiamare una stored procedure. Il codice di esempio alla fine di questo argomento fa riferimento a una stored procedure nel database di esempio Northwind, denominato CustOrdersOrders, definito come indicato di seguito.
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 usato nei parametri dell'oggetto comando. Accetta un parametro ID cliente e restituisce informazioni sugli ordini del cliente. Il seguente codice di esempio usa questa stored procedure come origine per un Recordset ADO.
L'uso della stored procedure consente di accedere a un'altra funzionalità di ADO: il metodo Refresh della raccolta Parameters. Usando questo metodo, ADO può compilare automaticamente tutte le informazioni sui parametri richiesti dal comando in fase di esecuzione. L'uso di questa tecnica pregiudica le prestazioni perché ADO deve eseguire una query sull'origine dati per le informazioni sui parametri.
Esistono altre differenze importanti tra il codice di esempio seguente e il codice nei parametri dell'oggetto comando, dove i parametri sono stati immessi manualmente. Prima di tutto, 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 comunicare ad ADO che il comando era una stored procedure.
Infine, nel secondo esempio si deve fare riferimento al parametro tramite l'indice quando si imposta il valore, perché potrebbe non essere noto 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à. Ci si potrebbe chiedere perché la posizione del primo parametro menzionato nella stored procedure (@CustomerID) è 1 anziché 0 (objCmd(1) = "ALFKI"). Il motivo è che 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