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

Convert VBA Function to T-SQL or some other object - what would be best? Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 3:34 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 9:17 AM
Points: 123, Visits: 342
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

Post #1465970
Posted Friday, June 21, 2013 9:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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 #1466245
Posted Friday, June 21, 2013 9:32 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 9:17 AM
Points: 123, Visits: 342
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.
Post #1466260
Posted Friday, June 21, 2013 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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 #1466273
Posted Friday, June 21, 2013 10:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 9:17 AM
Points: 123, Visits: 342
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.
Post #1466312
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse