[Webinar] Streamline your web hosting managementRegister Today


VB Function to UDF

Posted on 2010-04-07
Medium Priority
Last Modified: 2012-05-09
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, "  ", "")
        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 Function
    MsgBox "Err_ConcatString " & Err.Number & ": " & Err.Description
    Resume Exit_ConcatString
End Function

Open in new window

Question by:OxonDev
  • 3
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 30006975
>ParamArray StringValues()
as sql server does not have this concept of a paramarray, you cannot really replicate this vb function into a udf.

Author Comment

ID: 30013961
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...
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 30014448
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?

Author Comment

ID: 30015521
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?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 30017231
sorry, there is no  1-to-1 translation of what you are asking for.
you have to change some code ...

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question