VB Function to UDF

Hi all, I'm looking for a UDF equivalent in SQL Server 2000 for this VB/VBA function.  
Public Function ConcatString(ByVal Seperator As String, ParamArray StringValues() As Variant) As String
'---------------------------------------
'|Concatonate elements in passed array.|
'---------------------------------------
On Error GoTo Err_ConcatString
Dim varValue As Variant
Dim strOutput As String
    
    If Not IsArray(StringValues) Then GoTo Exit_ConcatString
    
'Loop thru' array evaluating array members and adding valid members to output string.
    For Each varValue In StringValues
        varValue = Nz(varValue, "")
        Do Until varValue = Replace(varValue, "  ", "")
            varValue = Replace(varValue, "  ", "")
        Loop
        If Not varValue = "" Then
            If Not strOutput = "" Then
                strOutput = strOutput & Seperator
            End If
            strOutput = strOutput & varValue
        End If
    Next varValue
    
'Return function value.
    ConcatString = strOutput
  
Exit_ConcatString:
    Exit Function
  
Err_ConcatString:
    MsgBox "Err_ConcatString " & Err.Number & ": " & Err.Description
    Resume Exit_ConcatString
End Function

Open in new window

LVL 6
OxonDevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>ParamArray StringValues()
as sql server does not have this concept of a paramarray, you cannot really replicate this vb function into a udf.
0
OxonDevAuthor Commented:
Hi angellll.  I'll settle for say passing a maximum of say 20 varchar parameter after the seperator parameter, it's the internals that are frustrating me...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as a function cannot have optional parameters, you will have to pass NULLs for example for all the parameters that are "unused" on every call.

apart from that:
what is the "usage" of this function? I mean, where does the "data" come for this function calling?
0
OxonDevAuthor Commented:
Rats I'd forgotten it doesn't act like a stored procedure.  The function was intended to be a catch all concat function as it is in VB.  Any suggestions?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, there is no  1-to-1 translation of what you are asking for.
you have to change some code ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.