SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 465
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


Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63983 Visits: 17974
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.

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)
Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 465
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63983 Visits: 17974
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.

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)
Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 465
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search