Create UDF (Function) with one parameter - returns True/False from VBA code

  • I have written a Rule Engine in MS Access (client) that uses linked tables with SQL Server Native Client to SQL Server 2008 R2.

    There are around 150 Binary Rules. Each Binary Rule has combinations of SQL and other logic. They are designed to return a True/False.

    These Binary Rules are the first tier for the Rule Engine conversion.

    The process is now to take the tested VBA and convert each Binary Rule to TSQL UDF.

    The next layer, a Status Rule - will take one parameter and then call on dozens of the Binary Rules. Depending on the status number, it will expect different Binary Rules to return some pattern of True or False.

    The code below is the most simple rule. The purpose is to get a template for conversion from VBA to TSQL.

    Function Rule71(ID_Wells As Integer) As Boolean

    ' Rule 71 Well is a Facility - Pass in Primary Key for Well, run SQL for this ID. If record returns then True/ else False

    Dim rstMisc As DAO.Recordset

    Dim rstExclude As DAO.Recordset ' excluded states

    Dim SQLMisc As String ' NOTE Added IP Date afterwards

    Dim SQLExclude As String ' use to exclude states

    Dim RuleResult As Integer

    10 On Error GoTo errTrap

    20 Rule71 = False ' false until proven true

    25 ' the ID_Wells parameter is passed in to SQL statement

    35 SQLMisc = "SELECT Wells.ID_Wells, Wells.Well_Name, Wells.ClassificationID FROM Wells WHERE (((Wells.ID_Wells)=" & ID_Wells & ") AND ((Wells.ClassificationID)=3));"

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

    50 If Not (rstMisc.EOF And rstMisc.BOF) Then rstMisc.MoveLast ' 1 (or more) record indicates a True 0 records a False

    60 If rstMisc.RecordCount > 0 Then

    70 Rule71 = True

    80 Else

    90 Rule71 = False

    100 End If

    110 Exit Function

    errTrap:

    120 If Err.Number <> 0 Then

    130 Debug.Print "Function Rule71 has problem with well " & ID_Wells

    140 Err.Clear

    150 End If

    End Function

    Thanks

  • So, what's the question again?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I think the question was to provide a template for converting a VBA function to SQL. The short answer is that there isn't one template that will be best the approach. It depends on what you want the function to accomplish. If this is a part of an exercise where you're converting the whole application from Access to SQL Server, then take a long look at your functions and try to get some economy of scale by coding similar functions in a single one and return multiple values in a table. You don't want to code the same thing 30 times in 30 different scalar functions.

    Also, having 150 scalar UDFs sounds like a recipe for disaster. UDFs suffer a performance problem when compared to ITVFs, but nothing everything is suited to an ITVF. Again, it all depends on what you're trying to do.

  • Yes, how to convert this one function to a UDF would be great.

    I appreciate the comment on the efficiency of a hundred UDFs. The 100 Base Rules will not all be run at once.

    The next tier Status Rule chooses a subset of 10 to 20 of the total 100 Base Rules to run for that situation.

    My apologies, realizing most SQL Server developers run thousounds or millions of records.

    This is just a single record each time.

    The total Database is a result set of regulatory values, less than 120 MB.

    There will only be one record at a time run from the client.

    Enterprise wide, this will run 1 record an average of every 10 seconds.

    The Rule Engine manages complex Compliance rules across different government agencies.

    The efficiency gain comes from central Rule management reducing the network boundry.

    Right now, the Status Rules along wiith the Base Rules (like the example above) are being run from the client over ODBC.

  • Seems a little odd but here are two ways you could do it. The first is a **ACK**Scalar Function**ACK**, the second is much the same logic but as an inline table valued function. In both cases I am using a property of the implicit conversion to bit. If the value is >= 1 the value of a bit will always be 1. 😉

    create function Rule71

    (

    @ID_Wells int

    )

    returns bit as begin

    declare @TrueFalse bit

    select @TrueFalse = COUNT(*)

    FROM Wells

    WHERE Wells.ID_Wells = @ID_Wells

    AND Wells.ClassificationID = 3

    return @TrueFalse

    end

    go

    create function Rule71_itvf

    (

    @ID_Wells int

    )

    returns TABLE

    as

    RETURN

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

    FROM Wells

    WHERE Wells.ID_Wells = @ID_Wells

    AND Wells.ClassificationID = 3

    end

    _______________________________________________________________

    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/

  • Thanks! I didn't think about that. (where is my V8 icon).

    I was busy cobbling this together when you responded.

    The Rule Engine components are designed to return each of the 100 Base Rules as a True - False based on Query criteria.

    Let me try that out and come back to mark it as an anwser.

    CREATE FUNCTION dbo.Rule71(@ID_Wells int) RETURNS int

    AS

    BEGIN;

    DECLARE @Result int;

    SET @Result = SELECT COUNT(*) AS "SELECT Wells.ID_Wells, Wells.Well_Name, Wells.ClassificationID FROM Wells WHERE (((Wells.ID_Wells)=" & @ID_Wells & ") AND ((Wells.ClassificationID)=3));

    IF @Result = 0

    Return 0; -- False

    else

    Return 1; -- True

    end;

    END;

    go

    SELECT R_71, dbo.Rule71(ID_Wells) AS Rule71

    FROM dbo.StatusTable;

  • Mile Higher Than Sea Level (6/19/2014)


    Thanks! I didn't think about that. (where is my V8 icon).

    I was busy cobbling this together when you responded.

    The Rule Engine components are designed to return each of the 100 Base Rules as a True - False based on Query criteria.

    Let me try that out and come back to mark it as an anwser.

    CREATE FUNCTION dbo.Rule71(@ID_Wells int) RETURNS int

    AS

    BEGIN;

    DECLARE @Result int;

    SET @Result = SELECT COUNT(*) AS "SELECT Wells.ID_Wells, Wells.Well_Name, Wells.ClassificationID FROM Wells WHERE (((Wells.ID_Wells)=" & @ID_Wells & ") AND ((Wells.ClassificationID)=3));

    IF @Result = 0

    Return 0; -- False

    else

    Return 1; -- True

    end;

    END;

    go

    SELECT R_71, dbo.Rule71(ID_Wells) AS Rule71

    FROM dbo.StatusTable;

    Instead of a scalar function I would use the itvf version. A slight change to your query will yield some decent performance gains.

    SELECT R_71, Rule71.MyResult

    FROM dbo.StatusTable

    cross apply dbo.Rule71(ID_Wells) as Rule71

    _______________________________________________________________

    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/

  • Thanks again! Found an article on that and will now take the time to read it.

Viewing 8 posts - 1 through 7 (of 7 total)

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