ByRef vs. global variables

[millerk]

What is best practice when a function needs to return multiple values?
I write a separate function for each stored procedure I call. Some of these need to return multiple values from stored procedure output parameters.
I've been using global variables for these situations, but I'm wondering if it would be better to use ByRef parameters.

Pros and cons?

 

[RiverGuy]

Why don't you just return an array?

 

[millerk]

Can I use an array with multiple types?
That sounds like more work than just using byref params or globals.

 

[RiverGuy]

Here is what I used to test. Other ideas are to return a collection, return the string values (if all values are coming from a database), and delimit the values, taking them apart later.

Public Function Test() As Object()
        Dim objs(2) As Object
        Dim s As String
        s = "Hello"
        objs(0) = s
        Dim i As Integer
        i = 377
        objs(1) = 1
        Dim b As Boolean
        b = False
        objs(2) = b
        Return objs
End Function

Dim MyObjects() As Object
        Dim o As Object
        MyObjects = Test
        For Each o In MyObjects
            MsgBox(o.GetType.ToString)
        Next

 

[millerk]

Using a structure looks like it may be a good option.
It will hold all the info I need to get back from my function that calls the stored procedure.

Public Structure UOMQuantity
Public ReturnValue As Integer
Public UOM As String
Public Quantity As Double
End Structure

 

[chiph]

If you're returning a large number of parameters, your method may be trying to do too much. Would it be possible to break it up into 2 or more smaller methods?

Otherwise, a class or a struct would be the way to go, especially if the values within were related in some way.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[millerk]

Breaking it up wouldn't be logical.
For each SQL stored procedure that gets called, I have a function in my app that takes the parameters, sets up the command object, executes the proc, and returns either a resultset, a single return value, or a number of ouput params depending on the proc.

I think using the structure makes the most sense for these functions.

 

[MickTheBelgian]

You could return a name-value collection where each parameter has the same name as in your Stored procedure. This way you could make your database procedures "independent". If you change the stored procedure, you just need to change the code receiving the collection, you don't need to change the code related to database-access.

 

[jfrost10]

You could use an array list. It doesn't care what type of objects you stick in there, and they can be of different types I beleive.

The only thing to watch is what you do with that array list at the other end, since the types will be all different.

D'Arcy