LISTING 2: VB Procedure That Calls usp_GetCatTotals to Return XML

Private Sub OpenProc(ByVal pCatID As Long, ByVal pStart As Date, ByVal pEnd As Date)

Dim cnConn As Connection
Dim strConn As String
Dim cmCmd As Command
Dim strQuery As String
Dim sResponseStream As Stream

BEGIN CALLOUT A
Set cnConn = New Connection

strConn = "Provider=SQLOLEDB;Data Source=ssosa\sql2000;Initial Catalog=Northwind;trusted_connection=yes"
cnConn.ConnectionString = strConn
cnConn.Open

Set cmCmd = New Command
Set cmCmd.ActiveConnection = cnConn

With cmCmd
   CommandType = adCmdStoredProc
   CommandText = "usp_GetCatTotals"
   Parameters.Append .CreateParameter("@CatID", adInteger, adParamInput, 4, pCatID)
   Parameters.Append .CreateParameter("@Start", adDBDate, adParamInput, 8, pStart)
   Parameters.Append .CreateParameter("@End", adDBDate, adParamInput, 8, pEnd)
End With
END CALLOUT A

BEGIN CALLOUT B
' Create a stream to handle the response
Set sResponseStream = New Stream
sResponseStream.Open

' Can also be a response object in an ASP page
cmCmd.Properties("Output Stream") = sResponseStream
END CALLOUT B

cmCmd.Execute , , adExecuteStream
   
BEGIN CALLOUT C
' Load the XML into the parser
mobjXML.Load sResponseStream

Call ShowXML
END CALLOUT C

Set cnConn = Nothing
Set cmCmd = Nothing
Set sResponseStream = Nothing

End Sub