명령으로 저장 프로시저 호출
명령을 사용하여 저장 프로시저를 호출할 수 있습니다. 이 항목의 끝에 있는 코드 샘플은 다음과 같이 정의된 CustOrdersOrders라는 Northwind 샘플 데이터베이스의 저장 프로시저를 참조합니다.
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS
SELECT OrderID, OrderDate, RequiredDate, ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
저장 프로시저를 정의하고 호출하는 방법에 대한 자세한 내용은 SQL Server 설명서를 참조하세요.
이 저장 프로시저는 명령 개체 매개 변수에 사용되는 명령과 유사합니다. 고객 ID 매개 변수를 사용하고 해당 고객의 주문에 대한 정보를 반환합니다. 다음 코드 샘플에서는 이 저장 프로시저를 ADO Recordset의 원본으로 사용합니다.
저장 프로시저를 사용하면 ADO의 다른 기능인 Parameters 컬렉션 Refresh 메서드에 액세스할 수 있습니다. 이 메서드를 사용하여 ADO는 런타임에 명령에 필요한 매개 변수에 대한 모든 정보를 자동으로 채울 수 있습니다. ADO는 데이터 원본에서 매개 변수에 대한 정보를 쿼리해야 하므로 이 기술을 사용하면 성능 저하가 발생합니다.
다음 코드 샘플과 매개 변수를 수동으로 입력한 명령 개체 매개 변수의 코드 간에는 다른 중요한 차이점이 있습니다. 첫째, 이 코드는 SQL Server 저장 프로시저이며 정의에 의해 미리 컴파일되므로 Prepared 속성을 True로 설정하지 않습니다. 둘째, Command 개체의 CommandType 속성이 ADO에 명령이 저장 프로시저임을 알리기 위해 두 번째 예제에서 adCmdStoredProc로 변경되었습니다.
마지막으로 두 번째 예제에서는 디자인 타임에 매개 변수의 이름을 알 수 없으므로 값을 설정할 때 인덱스로 매개 변수를 참조해야 합니다. 매개 변수의 이름을 알고 있는 경우 Command 개체의 새 NamedParameters 속성을 True로 설정하고 속성의 이름을 참조할 수 있습니다. 저장 프로시저(@CustomerID)에 언급된 첫 번째 매개 변수의 위치가 0(objCmd(1) = "ALFKI")이 아닌 1인 이유가 궁금할 수 있습니다. 이는 매개 변수 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