Since many client/server applications depend heavily on stored procedures (SP), a data access interface should be able to perform them quickly and efficiently. Stored procedures, however, can be tricky to handle. In some cases, stored procedures require management of OUTPUT and return status values and other, more conventional arguments. In addition, an SP can return several complex resultsets, including PRINT or RAISERROR statement return values. In some cases, it's better to create complex SPs, while in others it’s better and easier to keep SPs simpler and more modular and use Visual Basic to tie them together.
The code below shows the RDO approach to these problems. First, it performs a simple parameter-based SP and shows the results in the grid. Some accommodations are made to the subsequent ADO design, and these are noted. It uses the same connections established in the earlier examples.
Note that the code requires us to include a correct ODBC "Call" statement. Again, this is not necessary in the UserConnection designer, but it’s essential in the RDO code-based approach. Here, you use the stand-alone rdoQuery object and assign the already open Connection to it. The rdoQuery object can then be used in subsequent calls to handle a parameter query.
Note also that the code does not attempt to refer to the return status argument. This value is not available until the resultset is fully populated; only then does SQL Server return this value.
Private Sub RunSPButton_Click()
Dim Qy As New rdoQuery
Dim rs As rdoResultset
sql = "{? = Call AuthorByYearBorn (?,?)}"
Set Qy.ActiveConnection = cn
Qy.sql = sql
Qy.rdoParameters(0).Direction = rdParamReturnValue
Qy(1) = "1947"
Qy(2) = "1948"
Set rs = Qy.OpenResultset()
rdoGrid1.ShowData rs
ShowRows = rs.RowCount
rs.Close
End Sub
ADO has a lot of flexibility when it comes to performing stored procedures. But this flexibility comes at the cost of more code. As with the previous example ("Performing a Parameter Query"), it's possible to build your own ADODB Parameters collection. In this case, you’re performing a simple two-argument SP, "AuthorByYearBorn", that returns a small resultset. Note that although ADO allows you to create your own parameters, it's not necessary to do so.
Note ADO collections are 0-based to match DAO. RDO collections are 1-based.
Private Sub RunSPButton_Click()
Dim Qy As New ADODB.Command
Dim Parm As New ADODB.Parameter
Dim Parm2 As New ADODB.Parameter
Set Qy.ActiveConnection = cn
Qy(0)="1947"
Qy(1)="1948"
Qy.CommandType = adCmdStoredProc
Qy.CommandText = "AuthorByYearBorn"
Set rs = Qy.Execute(ShowRows)
ADOGrid1.ShowData rs
End Sub