Executing a SQL Server Stored Procedure By Using ActiveX Data Objects
If you are doing an ADO development with client server for backends, then you probably
call stored procedures. In doing so, you will use the ADO Command object, as well as
the Parameter object if you are passing parameters.
You will create a Command object and supply the command text, which in this case will
be the name of the stored procedure, called CustOrdersHist. You can see the T-SQL for
CustOrderHist in Listing A.7. This stored procedure returns product names and the total
quantity purchased of those products for a given customer.
Listing A.7 Northwind SQL Server Database: T-SQL for the Stored Procedure
Called CustOrdersHist
ALTER PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND _
OD.ProductID = P.ProductID
GROUP BY ProductName
You will then specify the type of Command object you are creating-in this case by using
the type of ADODB.CommandTypeEnum.adCmdStoredProc.
The next step is to create a parameter that the Command object will use. This parameter
will match the one specified in CustOrdersHist, called CustomerID. You can see the
actual code for this routine, called UseAStoredProcedureWithAParameter, in Listing A.8.
Listing A.8 basCommandExamples.vb: Calling a Stored Procedure By Using
Parameters
Sub UseAStoredProcedureWithAParameter(ByVal txtResults As TextBox)
Dim cnn As New ADODB.Connection()
Dim rstCurr As New ADODB.Recordset()
Dim cmd As New ADODB.Command()
Dim prm As ADODB.Parameter
Try
cmd.CommandText = "CustOrderHist"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
prm = cmd.CreateParameter("CustomerID", ADODB.DataTypeEnum.adChar,
ADODB.ParameterDirectionEnum.adParamInput, 5)
cmd.Parameters.Append(prm)
prm.Value = "CHOPS"
OpenNorthwindADOConnection(cnn)
cmd.ActiveConnection = cnn
rstCurr.Open(cmd)
txtResults.Text = rstCurr.GetString
Catch excp As Exception
MessageBox.Show(excp.Message)
End Try
End Sub
The last thing that this routine does is open a recordset based on the Command object.
This is to the use just those records that are needed. In this case, the GetString method is
used to assign it to the results text box. If you are using a bulk query, shown in the next
section, you would use the Execute method. To see the routine in A.8 executed, click on
the button with the caption Stored Procedure with Parameter, located on the frmMain
form for this Appendix project.