Accessing Standard Procedures that are functions via VB

[bradohare]

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

[szlamany]

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.

    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).

    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

[bradohare]

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