SQL functions to access

[Pauldohert]

I have functions written in SQL - what the most efficient way of passing a value from access and getting the return to access?


Thanks

 

[pdx_man]

You can do this a couple ways depending on the type of Functions you have. A Table Function call is different than Scalar Function call. The most scalable would probably be to write a wrapper SP to handle the different types, and update the parameters. Here is the VBA that I use to dynamically set the parameters for a Stored Procedure call:

Public Function Update_PassThrough_Parameters(varQueryName As String, varParams As String) As Integer
Dim QryDef As dao.QueryDef
Dim varQuerySQL As String
Dim varCommaPos As Integer
Dim varNextParam As String

On Error GoTo SomethingDidNotUpdate

    Set QryDef = CurrentDb.QueryDefs(varQueryName)
    varQuerySQL = Left(QryDef.SQL, InStr(QryDef.SQL, "'") - 1)
    
    Do
        varCommaPos = InStr(varParams, ",")
        varNextParam = GetNextCSV(varParams, varCommaPos)
        varParams = Mid(varParams, InStr(varParams, ",") + 1)
        
        If IsNumeric(varNextParam) Then
            varQuerySQL = varQuerySQL & varNextParam & ", "
        Else
            varQuerySQL = varQuerySQL & AddSingleQuotes(varNextParam) & ", "
        End If
    Loop Until varCommaPos = 0

    varQuerySQL = Left(varQuerySQL, Len(varQuerySQL) - 2)
    QryDef.SQL = varQuerySQL
    
    Update_PassThrough_Parameters = 1
    
    Exit Function
    
SomethingDidNotUpdate:
        Update_PassThrough_Parameters = Err.Number
        
End Function

So then your Pass-Through query (named Qry_Func_PT) would look like this:

EXEC YourDB.dbo.proc_FunctionWrapper 'Func_Type', 'FunctionName', 'Param1', 'Param2',

Your call to update this with your parameters would be something like:

If Update_PassThrough_Parameters ("Qry_Func_PT", "fn_Mult, 5, 3") = 1 Then
Set rst = CurrentDB.OpenRecordset("Qry_Func_PT")
Else
msgbox "There was an error updating your Parameters"
End If

Your wrapper SP would look like:

Create Procedure dbo.proc_FunctionWrapper (@Func_Name VARCHAR(25), @p1 = SQL_VARIANT, @p2 SQL_VARIANT = NULL, @p3 SQL_VARIANT = NULL, @p4 SQL_VARIANT = NULL ...) AS

If @Func_Name = 'fn_SomeTableFunc'
SELECT ThisField, ThatField FROM TheDB.dbo.fn_SomeTableFunc(@p1, @p2, @p3)
ELSEIF @Func_Name = 'fn_Mult'
SELECT @p1, @p2, dbo.fn_Mult(@p1, @p2)
:
:

I have not tested this, but this should give you the framework.