Need some help on converting VBA function to T-SQL 1

[jseptor1]

I'm a complete newbie to T-SQL, but have a pretty solid understanding of constructing SQL statements (queries), and VBA. I'm working on a project converting an access database into a SQL database (no front end will remain in access).

I'm hoping someone can tell me this will be simple, and if not, help point me in the right direction!

Here's my VBA function I need to make work in SQL as a stored procedure: (SQL Table / field names are the same as the ones referenced here)

Public Function getText(var_OID, var_SYNTAX, var_VALUE)
Dim strTemp, strValue As String

strTemp = var_OID

Select Case var_SYNTAX
Case "OBJECT IDENTIFIER"
'field contains object identifier
getText = DLookup("my_description", "tblLrcsMib", "my_OID = '" & var_VALUE & "'")
Exit Function
Case "TimeTicks"
'field contains time ticks
getText = "timeTicks"
Exit Function
Case Else
'field doesn't contain object identifier, keep removing 1 from the OID until it's resolved against the MIB
Do Until getText <> ""
'lookup modified OID in MIB table
getText = DLookup("my_description", "tblLrcsMib", "my_OID = '" & strTemp & "'")
'Found matching MIB, quit the function
If getText <> "" Then
Exit Function
Else
'didn't find matching MIB, remove another character from the OID
strTemp = Left(strTemp, Len(strTemp) - 1)
End If
Loop
End Select

End Function

 

[SimonSellick]

If you want a return value that isn't a recordset (a string according to your VBA source), then you probably mean a function, not a stored procedure.

T-SQL has a BASIC-like syntax, but without the clever bits. You can write your function iteratively:

1) change the header to a CREATE FUNCTION statement (use the T-SQL help in Query Analyser for syntax) returning a varchar

2) change string declarations to varchars of appropriate length; again use the T-SQL help for syntax of declarations (note: all variables start with '@')

3) change

select case var case val action ... end select

to

case var when var then action ... end

4) change

var = DLookUp(...)

to

select var = ... from ... where etc (must return 1 row)

5) I'm not sure, but I don't think that T-SQL has a do until, so you'll need to invert the condition and use while.

6) All the other silly bits that I've forgotten (sorry no SQL Server available to me at present).

Hope it helps a little; at least to get you started. Query Analyser also has a debug mode that is pretty good if enabled - again, try the help.

 

[PWise]

how do i get qa into debug mode

pwise

 

[SimonSellick]

I believe that it is a right-click option for functions and procs in the browser panel.

 

[jseptor1]

Thanks for the response, it was exactly the type of response I needed.

 

[PWise]

thank you simon have been working with qa for 4+ years and did not know about this feature. what else dont i know? have a star