Avatar of Sameer Khanna
Sameer Khanna
 asked on

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

DatabasesMicrosoft AccessVBAMicrosoft SQL Server

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
PatHartman

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

what is doing?

give us a sample...
Sameer Khanna

ASKER
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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question