June 18, 2014 at 4:47 pm
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
June 18, 2014 at 5:18 pm
June 18, 2014 at 8:28 pm
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.
June 19, 2014 at 10:35 am
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.
June 19, 2014 at 10:56 am
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/
June 19, 2014 at 1:37 pm
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;
June 19, 2014 at 1:40 pm
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/
June 19, 2014 at 2:09 pm
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