Accessing Standard Procedures that are functions via VB

Hello,

I'm having trouble figuring out how to resolve my current issue with accessing the result of a stored procedure. The stored procedure is a function and returns a text value (the function itself returns the value, NOT via parameter). Everything I've read online deals with attaching a stored procedure that returns a record set and traversing the results or reading an output parameter that was passed. These procedure's are third party so I don't have the option of adding an output parameter and placing my return value in there.

Any thoughts?

thanks
brad

Is it a stored procedure or is it a user-defined function - you say both.

My first thought is that a stored procedure can only RETURN a numeric value. Any text being passed back need to come back in the form of OUTPUT PARAMETERS.

OUTPUT parameters have problems of their own. If a recordset is also returned from the SPROC, then it must be read fully before the OUTPUT parameters appear - ADO is very sequential in this area...

How are you calling the SPROC or UDF right now??

Here's a code snippet I dug up for returning a UDF value as a RECORDSET - if that's what you are doing.

Code:

---

    rs.Open "SELECT dbo.GetConf('" & ConfItem _
        & "') as ConfData", gCn
    GetConf = rs!ConfData & ""

---

Otherwise here is some ADO for calling a SPROC and looking at OUTPUT parameters - there is no recordset returned by these SPROCS (and they use SET NOCOUNT ON at the top of each SPROC to avoid ADO issues with "RECORDSET AFFECTED" output data).

Code:

---

    With objCmd
        .CommandText = s1
        .ActiveConnection = gCn
        .CommandType = adCmdStoredProc
       
        .Parameters.Append .CreateParameter("@PassConnId", adInteger, adParamInput, 8, glngConnId)
        .Parameters.Append .CreateParameter("@RetStat", adInteger, adParamOutput, 8)
        .Parameters.Append .CreateParameter("@RetText", adVarChar, adParamOutput, 100)
        .Parameters.Append .CreateParameter("@RetMode", adInteger, adParamOutput, 8)
        .Parameters.Append .CreateParameter("@RetGrid", adInteger, adParamOutput, 8)
        .Parameters.Append .CreateParameter("@RetExtra", adVarChar, adParamOutput, 100)
        .Parameters.Append .CreateParameter("@PassMode", adInteger, adParamInput, 8, f.mintFormState)
        .Parameters.Append .CreateParameter("@PassExtra", adVarChar, adParamInput, 10, strSaveExtra)
        .Parameters.Append .CreateParameter("@PassTest", adChar, adParamInput, 1, "")
       
        .Execute
        lngUpdCnt = lngUpdCnt + 1
    End With

    Debug.Print "      SaveGrid>@RetStat="; objCmd.Parameters(1).Value

    Select Case CLng(objCmd.Parameters(3).Value)
        Case Is > 0
            SaveGrid = CLng(objCmd.Parameters(3).Value)    ' @RetMode
        Case Is = -1
            strErrMsg = objCmd.Parameters(2).Value
    End Select

---

Thanks for the response.

I'm actually calling an Oracle package they've provided. If I'm in SQL Plus and make the call the result is a bunch of text.

Because it's their package I using a parameter for output is not any option.

Any thoughts?

-brad