Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create UDF (Function) with one parameter - returns True/False from VBA code Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2014 4:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
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

Post #1583462
Posted Wednesday, June 18, 2014 5:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 3,576, Visits: 8,026
So, what's the question again?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1583473
Posted Wednesday, June 18, 2014 8:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 3,983, Visits: 3,421
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1583524
Posted Thursday, June 19, 2014 10:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
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.

Post #1583968
Posted Thursday, June 19, 2014 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1583972
Posted Thursday, June 19, 2014 1:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
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;

Post #1584052
Posted Thursday, June 19, 2014 1:40 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1584053
Posted Thursday, June 19, 2014 2:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:57 PM
Points: 123, Visits: 347
Thanks again! Found an article on that and will now take the time to read it.
Post #1584055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse