I am trying to call a SQL Server Stored procedure that takes one (1) input parameter and one (1) output parameter. Input parameter is of type string and the output parameter is also of type string. I DO NOT want a record set as the return value. I am using “*lsxodbc” to call the stored procedure.
I am giving below the code that I have running currently that returns a record set by calling a stored procedure “test”. What modifications do I need to make to the code for getting a string as an output instead of a record set? The stored procedure is in place to return the string output.
----Code--------------------------------------
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
Set qry.Connection = con
status = con.ConnectTo("ABC", "xxx", "xxxx" )
If status = False Then
Messagebox("Testing SQL Server Stored Procedure: Not Connected")
Exit Sub
End If
'---- calling stored procedure---
Qry.SQL = "{CALL test}" --- How do I pass input and output parameters here?
Set result.Query = qry
bLastRetCode = result.Execute()
'Exit if Execute failed
If bLastRetCode = False Then
strErrorMessage = result.GetExtendedErrorMessage
Messagebox(strErrorMessage)
Else
'result.lastrow
'nRows = result.NumRows
'Messagebox(nRows)
End If
count% = result.NumColumns
Messagebox(count%)
If Not result.IsResultSetAvailable Then
Messagebox "Couldn't get result set"
Exit Sub
End If
Do
result.NextRow
temp_var = result.GetValue("Field_Name")
Messagebox(temp_var)
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
con.Disconnect