Performing a Parameter-Driven Stored Procedure

See Also

RDO

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

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