How To Call a Parameterized SQL Server Stored Procedure from RDO (228800)

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=<username>;pwd=<strong password>;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