Convert VBA Function to T-SQL or some other object - what would be best?

  • An Access Programmer - with Linked SQL Tables - Any ideas on the best way to approach this design would be welcome?

    Here is a simple function that returns a True/False written in MS Access.

    It is part (1 of 180) of a Rules Engine. In a MS Access Query for example, the query can call

    Column1 [ID_Building] Comun2 StakingStatus: Building_Status_Staking_Does_Building_FieldWorkDate_Land([ID_Building])

    The output would be something like 343434 True

    For small pulls of a dozen records, it cost a few mili-seconds. The SQL Server Native Client actually does convert it to some sloppy T-SQL.

    The problem is - I will soon have about 120 columns with more complex rules.

    Times around 100 to 500 concurrent users.

    Should I use a Stored Procedure, a TSQL pass-through query, or other method?

    This is a Rules Engine under development. The rules are run when one of many key fields across many forms are changed.

    Each user passes in a single [ID_Building] for a session that has around 30 forms (not all open of course) If they change one value, the rules engine evaluates all of the rules and updates a dashboard that needs to give the appearance of "real-time". (e.g. within 1 to 10 seconds).

    Think about a tax form: change a dependent from 1 to 2, you are a homeowner, 2 incomes, with rental income, .... - the change from 1 to 2 activates a Dashboard that "recommends you file separately"

    Public Function Building_Status_Staking_Does_Building_FieldWorkDate_Land(ID_Building) As Boolean

    ' Rule Building Status - Staking Staking Status Can NOT have an a Field Work Date of type LAND

    Dim rstMisc As DAO.Recordset

    Dim SQLMisc As String ' NOTE Added IP Date afterwards

    10 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False ' set function to false until proven true

    SQLMisc = "SELECT APD_FieldWorkDate_2.ID_Buildings, APD_FieldWorkDate_2.ID_Bio_Svy_Type FROM APD_FieldWorkDate_2 " & _

    "WHERE (((APD_FieldWorkDate_2.ID_Buildings)=" & ID_Building & ") AND ((APD_FieldWorkDate_2.ID_Bio_Svy_Type) In (15,18)));"

    30 Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)

    40 On Error Resume Next

    50 rstMisc.MoveLast

    60 If rstMisc.RecordCount > 0 Then

    70 Building_Status_Staking_Does_Building_FieldWorkDate_Land = True

    80 Else

    90 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False

    100 End If

    110 If Err.Number <> 0 Then

    120 Err.Clear

    130 Exit Function

    140 End If

    ' A Case statement would be OK too.

    End Function

  • Pretty tough to tell what you are trying to do here but it seems like all the VBA is doing is running a query and seeing how many rows it has? You could turn this into an iTVF pretty easily like this:

    create function Building_Status_Staking_Does_Building_FieldWorkDate_Land

    (

    @ID_Building int --Not sure of the datatype here???

    )

    returns table

    return

    select cast(COUNT(*) as bit) as MyResult

    FROM APD_FieldWorkDate_2 fwd

    WHERE fwd.ID_Buildings = @ID_Building

    AND fwd.ID_Bio_Svy_Type In (15, 18)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Yes it is an integer.

    What I am attempting to return is a True / False (meta data) - not the actual records.

    THANKS!! A function - why didn't I think of that?

    Have not thought of a function, that is a great solution that matches my Access side.

    Next question: Can a function be called directly in a TSQL Query?

    My goal is to have many functions that return a True/False.

    They all use the identical ID_Building as the argument.

    The idea is to receive a single record with the ten columns of True/False

    e.g. 29992 T T T F F F T T F T

    Each of these function results feed a Dashboard about regulatory data in the user interface.

    Can SQL functions be called directly in a SQL TSQL query statement?

    A simple example would probably get me up and running.

  • Mile Higher Than Sea Level (6/21/2013)


    Yes it is an integer.

    What I am attempting to return is a True / False (meta data) - not the actual records.

    THANKS!! A function - why didn't I think of that?

    Have not thought of a function, that is a great solution that matches my Access side.

    Next question: Can a function be called directly in a TSQL Query?

    My goal is to have many functions that return a True/False.

    They all use the identical ID_Building as the argument.

    The idea is to receive a single record with the ten columns of True/False

    e.g. 29992 T T T F F F T T F T

    Each of these function results feed a Dashboard about regulatory data in the user interface.

    Can SQL functions be called directly in a SQL TSQL query statement?

    A simple example would probably get me up and running.

    It is important to understand that what I put together is not a scalar function. It is an inline table valued function. That means you can join to it just like a table. You would have to join to it multiple times in your query.

    You might want to check BOL for table valued functions. It will provide far better details than I can rattle off.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-1.aspx

    Thanks again! I was on the phone talking to my friend, a very senior SQL programmer who was waiting for his flight.

    Was reading the article above and coming to the conclusion.

    He told me the evils of scalar - per my question. Wow, what a difference.

    I said "Now I understand why the SQL ServerCentral post was pointing me into the right direction.

    It totally makes sense now. I will read your articles over the weekend.

    This is very exciting. Will do my homework and start on this.

    If there are any tools that would help a Newbie, please let me know.

    Thank you again for your time and help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply