Microsoft KB Archive/228800

= How To Call a Parameterized SQL Server Stored Procedure from RDO =

Article ID: 228800

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q228800



SUMMARY
This article shows how the rdoQuery object can be used to call a parameterized SQL Server stored procedure.



MORE INFORMATION
The sample in this article uses the Pubs database in SQL Server to demonstrate the use of the RDO query object to call parameterized queries. The Pubs database contains a stored procedure called byroyalty that accepts an integer parameter and returns all authors from the Titleauthor table whose royalty amount matches the passed value. Although this stored procedure does not return any values to the caller, the example reserves space for a return value in the Parameters collection for illustrative purposes.

Create and execute the following program. It calls the byroyalty stored procedure in the SQL Server Pubs sample database. Then, it prints values from the returned recordset in the debug window. Substitute a valid Server, User ID and Password in the connection string.

Sample Code
Private Sub Command1_Click

Dim rs As rdoResultset Dim cn As New rdoConnection Dim qd As New rdoQuery Dim cl As rdoColumn cn.Connect = "uid= ;pwd= ;server=MyServer;" _ & "driver={SQL Server};database=pubs;" _ & "DSN='';" cn.CursorDriver = rdUseOdbc cn.EstablishConnection rdDriverNoPrompt

Set qd.ActiveConnection = cn qd.SQL = "{ ? = call dbo.ByRoyalty (?) }" qd(0).Direction = rdParamReturnValue qd(1).Direction = rdParamInput

qd.rdoParameters(1) = 100

Set rs = qd.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

For Each cl In rs.rdoColumns Debug.Print cl.Name, Next Debug.Print

Do Until rs.EOF For Each cl In rs.rdoColumns Debug.Print cl.Value, Next rs.MoveNext Debug.Print Loop

rs.Close qd.Close cn.Close

End Sub

