Convert VBA UDF to SQl SERVER UDF

Hello All,

I recently did the migration of MS Access to SQL Server 2014 Express edition and I have only moved Tables to SQL Servers and few straight forward queries are done as pass through Queries but i have one Query that's taken a week for me to resolve with no results and that query calls the VBA function and it takes lot of time. Can someone please suggest how can i convert this function to SQL Server UDF
Public Function Comp(sql As String) As String
Dim db As Database, rs As Recordset, i As Long, Res As String, Tmp As String
Dim Stelle As Variant, drin As Variant
Dim Part_SQL1 As String, SQL1 As String, SQL2 As String

  On Error Resume Next
  Set db = CurrentDb()
  Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
  
  If Err <> 0 Then
    Res = "#Fehler"
  Else
    On Error GoTo 0
    Res = ""
    Do While Not rs.EOF
        
       SQL1 = rs(0)
       SQL2 = rs(1)
       'If SQL1 Or SQL2 = "" Then
       '   GoTo Weiter
       'End If
       Do
          Stelle = InStr(1, SQL1, ",")
          If Stelle = 0 Then
             If Left(SQL1, 1) = " " Then
                Part_SQL1 = Mid(SQL1, 2)
             Else
                Part_SQL1 = SQL1
             End If
          Else
             Part_SQL1 = Left(SQL1, Stelle - 1)
          End If
          drin = InStr(1, SQL2, Part_SQL1)
          
          If Left(Part_SQL1, 1) = " " Then
             Part_SQL1 = Mid(Part_SQL1, 2)
          End If
          
          If drin <> 0 Then
             Res = Res & ", " & Part_SQL1
          End If
          SQL1 = Mid(SQL1, Stelle + 1)
       Loop Until Stelle = 0
       Res = Mid(Res, 3)
       rs.MoveNext
    Loop
    rs.Close
  End If
  
WEITER:
   Comp = Res
End Function

Open in new window

Sameer KhannaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

PatHartmanCommented:
Can you tell us in words what this function is doing?  It looks like it is reading through a recordet and concatenating stuff.

Since you don't know how to write this as a SQL Server function (and neither do I), it might be best to just start a Gig and pay someone to do it for you.
0
HainKurtSr. System AnalystCommented:
what is doing?

give us a sample...
0
Sameer KhannaAuthor Commented:
To be honest even i don't know what this function does..... :( I had hired a consultant from freelancer.com but they couldn't complete it, neither they were able to optimize the query nor this function .... Let me try what best i could do.... If anyone could help to suggest improvements in the query i would be glad

SELECT DISTINCT Datensätze.Datensatz, Datensätze.[A-Sachnummer], Datensätze.ZGS, Datensätze.Konfiguration, Datensätze.Zertifizierungsnummer, Datensätze.Applikationsfamilie, Applikationsvarianten.*, Motorvarianten.*, Baumuster.Baumuster, Baumuster.Code, Baumuster.Einbau, Datensätze.Ölwannen, [qry Ölwanne Beschreibung].Ölwanne1, [qry Ölwanne Beschreibung].Ölwanne2, [qry Ölwanne Beschreibung].Ölwanne3, [qry Ölwanne Beschreibung].Ölwanne4, [qry Ölwanne Beschreibung].Ölwanne5, [qry Ölwanne Beschreibung].Ölwanne6, [qry Ölwanne Beschreibung].Ölwanne7, [qry Ölwanne Beschreibung].Ölwanne8, Datensätze.Lüfterkupplungen, [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 0], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 1], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 2], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 3], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 4], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 5], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 6], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 7], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 8], [qry Lüfterkupplungen-Beschreibung].[Lüfterkupplung 9], Datensätze.[Status (A, B, C, F, V, P)], Datensätze.[ATL-Nummer], Datensätze.[Düsen-Art], Datensätze.Kolbennummer, Datensätze.[Nockenwellen-Nummer], Datensätze.Emissionstechnologie, Datensätze.[SPS-Nummer], Datensätze.[Zylinderkopf-Nummer], Datensätze.Überströmventil, IIf(IsNull([Datensätze].[ATL-Nummer]),[Motorvarianten].[ATL-Nummer],[Datensätze].[ATL-Nummer]) AS ATL_korr, IIf(IsNull([Datensätze].[Düsen-Art]),[Motorvarianten].[Düsen-Art],[Datensätze].[Düsen-Art]) AS Düsen_korr, IIf(IsNull([Datensätze].[Kolbennummer]),[Motorvarianten].[Kolbennummer],[Datensätze].[Kolbennummer]) AS Kolben_korr, IIf(IsNull([Datensätze].[Nockenwellen-Nummer]),[Motorvarianten].[Nockenwellen-Nummer],[Datensätze].[Nockenwellen-Nummer]) AS NoWe_korr, IIf(IsNull([Datensätze].[Emissionstechnologie]),[Motorvarianten].[Emissionstechnologie],[Datensätze].[Emissionstechnologie]) AS EmTech_korr, IIf(IsNull([Datensätze].[SPS-Nummer]),[Motorvarianten].[SPS-Nummer],[Datensätze].[SPS-Nummer]) AS SPS_korr, IIf(IsNull([Datensätze].[Überströmventil]),[Motorvarianten].[Überströmventil],[Datensätze].[Überströmventil]) AS Ü_ventil_korr, IIf(IsNull([Datensätze].[Zylinderkopf-Nummer]),[Motorvarianten].[Zylinderkopf-Nummer],[Datensätze].[Zylinderkopf-Nummer]) AS ZylKoNr_korr, Datensätze.Bemerkungen AS Ausdr, IIf([Datensätze].[Status (A, B, C, F, V, P)]="V" Or "P",[Datensätze].[Bemerkungen],"") AS Bemerk_korr, ATL.Bezeichnung, Düsen.HD, Düsen.[SK-Winkel], Düsen.Lochzahl, Kolben.Verdichtung, Nockenwellen.C1000, Nockenwellen.[H in OT], Nockenwellen.[Grundkreis-Radius], Emissionstechnologie.AGN, Emissionstechnologie.innermotorisch, Datensätze.CAL_ID, Datensätze.Ersteller, Datensätze.Erstelldatum, Datensätze.[Einsatz Termin], IIf(Not IsNull([Einsatz_MV]),Comp("SELECT Einsatz, Einsatz_MV FROM [qry Einsatz DS900 Euro4_2] WHERE [ID-Datensatz] = " & [ID-Datensatz]),[Einsatz]) AS Einsatz_Toni, Datensätze.Zertifiziert
FROM Motorvarianten INNER JOIN ((Applikationsvarianten LEFT JOIN Baumuster ON Applikationsvarianten.Applikationsvariante = Baumuster.Applikationsvariante) RIGHT JOIN (Nockenwellen RIGHT JOIN (Kolben RIGHT JOIN (Emissionstechnologie RIGHT JOIN (Düsen RIGHT JOIN (ATL RIGHT JOIN ((Datensätze LEFT JOIN [qry Ölwanne Beschreibung] ON Datensätze.Ölwannen = [qry Ölwanne Beschreibung].Ölwannen) LEFT JOIN [qry Lüfterkupplungen-Beschreibung] ON Datensätze.Lüfterkupplungen = [qry Lüfterkupplungen-Beschreibung].Lüfterkupplungen) ON ATL.[ATL-Nummer] = Datensätze.[ATL-Nummer]) ON Düsen.[Düsen-Art] = Datensätze.[Düsen-Art]) ON Emissionstechnologie.Emissionstechnologie = Datensätze.Emissionstechnologie) ON Kolben.Kolbennummer = Datensätze.Kolbennummer) ON Nockenwellen.[Nockenwellen-Nummer] = Datensätze.[Nockenwellen-Nummer]) ON Applikationsvarianten.Applikationsvariante = Datensätze.Applikationsvariante) ON Motorvarianten.Motorvariante = Datensätze.Motorvariante
WHERE (((Datensätze.[Status (A, B, C, F, V, P)])="V" Or (Datensätze.[Status (A, B, C, F, V, P)])="P"));

Open in new window

0
PatHartmanCommented:
If you can't tell us what it is supposed to do, none of us can even take a shot at it for free and you certainly will be unhappy with someone coding the function for money if you can't tell them what it is supposed to do.
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
Databases

From novice to tech pro — start learning today.