troubleshooting Question

Convert VBA UDF to SQl SERVER UDF

Avatar of Sameer Khanna
Sameer Khanna asked on
DatabasesMicrosoft AccessVBAMicrosoft SQL Server
4 Comments1 Solution181 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros