Help Convert VBA to Stored Procedure SP (1 Viewer)

Rx_

Nothing In Moderation
Local time
Yesterday, 19:35
Joined
Oct 22, 2009
Messages
2,803
What is the best way to turn a Function into TSQL?
How would this be done for example in a SQL View rather than a Access Linked Table Query?

An Access Query used the following function to return a True/False.
Column 1 in the query is [Well_ID]
Column 2 in the query is FWD: Well_Status_Staking_Does_Well_FieldWorkDate_Land([Well_ID])

If there are records in a 2nd table - then True/ else False.
This Master Rule has about 20 of these to check - Then there is a Result column that Add Up T/F for a resultant T/F (each row).
This is metadata that is the result of a rule engine.
10,000 Records time 20 similar but different functions (columns) takes too long.
An SP or other construct should be better.


Code:
Public Function Well_Status_Staking_Does_Well_FieldWorkDate_Land(ID_Well) As Boolean ' Does this well have Notice of Staking? May or may not have
      ' Rule Well Status - Staking        Staking Can NOT have an a Field Work Date of type LAND
      Dim rstMisc                         As DAO.Recordset  
      Dim SQLMisc         As String  ' 
10      Well_Status_Staking_Does_Well_FieldWorkDate_Land = False  ' false until proven true
        SQLMisc = "SELECT APD_FieldWorkDate_2.ID_Wells, APD_FieldWorkDate_2.ID_Bio_Svy_Type FROM APD_FieldWorkDate_2 " & _
                    "WHERE (((APD_FieldWorkDate_2.ID_Wells)=" & ID_Well & ") 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                  Well_Status_Staking_Does_Well_FieldWorkDate_Land = True
80              Else
90                  Well_Status_Staking_Does_Well_FieldWorkDate_Land = False
100             End If
110             If Err.Number <> 0 Then
120                 Err.Clear
130                 Exit Function
140             End If
End Function

If there is a TSQL tool that would help, let me know. I have around 1,600 of these to do. This is a really simple one.
 

Rx_

Nothing In Moderation
Local time
Yesterday, 19:35
Joined
Oct 22, 2009
Messages
2,803
The best suggestion is to create TSQL Functions. The Functions can be used in a TSQL Query much along the line of an Access Query.

If anyone has suggestions on how to learn this process efficiently, please contribute.

Talked to a seniour SQL developer. One of the important things to do is to have a return type of Table rather than True/False. Then, Select the table. The reason is that the Table allows Parallel Processing. That is what this article explains.
http://sqlblog.com/blogs/hugo_korne...ons-the-good-the-bad-and-the-ugly-part-1.aspx

While I take some casual TSQL coding reading home over the weekend, I will plan to share if anyone indicates it might be of interest.

Here is a short example of the code above in TSQL
Code:
create function Building_Status_Staking_Does_Building_FieldWorkDate_Land
(
	@ID_Building int 
)
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)
 
Last edited:

Users who are viewing this thread

Top Bottom