How to call a SQL UDF from a cell in Excel

[RRinTetons]

Excel 2007SQL
Server 2005

I have a udf in SQL Server that returns a decimal(16,2) value. It takes 5 arguments. I want to get that value into a cell in an Excel worksheet using cell references as the arguments.

I can work with SQL Server through queries that return a recordset, but in this case I just want the single value, as if it were coming back from an Excel function.

I'm guessing there is a way to wrap the udf from SQL Server in a VBA function, but all I can find in references is how to get back a table. I thought that maybe I could treat the return value of the function as a 1 x 1 table, but that doesn't seem to work.

Any help on this?

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

Here's an example of a Oracle DB call within a function that my users use as spreadsheet functions...

Function GetNomen(sPN As String) As String
'SkipVought/2006 Mar 7/
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
':this function returns nomenclature for a given part number
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "dwprod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT PM.Nomen_201 "
    sSQL = sSQL & "FROM FRH_MRP.PSK02101 PM "
    sSQL = sSQL & "WHERE PM.PARTNO_201 like '" & Trim(sPN) & "%' "
    
    Debug.Print sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    On Error Resume Next
              
    rst.MoveFirst

    If Err.Number = 0 then
      GetNomen = rst("NOMEN_201")
    Else
      GetNomen = ""
    End If

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

That should get me started. Thanks!


-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

OK, progress, but I'm having trouble getting the connection open. In the code below I get back 'Hmmmmm' when I don't call cnn.Open (as commened out) and I get back #VALUE and execution stops on cnn.Open (strConn) as soon as I uncomment it. I'm flailing a bit with the syntax. My tactic is to get the connection opened (and closed), then to get it to return something, then to get it to call my function with the passed in args.

Function GetLaborData(dCurr As Date, _
                        dPrev As Date, _
                        sCoNum As String, _
                        sDeptNum As String, _
                        sAcctNum As String) As String

    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim strConn As String

    Set cnn = New ADODB.Connection
    strConn = "Provider=SQLNCLI10.1;"
    strConn = strConn + "Data Source=kansas;"
    strConn = strConn + "Initial Catalog=Directory;"
    strConn = strConn + "Integrated Security=SSPI;"
    strConn = strConn + "Auto Translate=False"
                
'    cnn.Open (strConn)
'
'    Set rst = New ADODB.Recordset
'
'    sSQL = "SELECT 'Bill'"
'
'    Debug.Print sSQL
'
'    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
'    On Error Resume Next
'
'    rst.MoveFirst

    GetLaborData = "Hmmmmm"

'    rst.Close
'    cnn.Close
'
'    Set rst = Nothing
    Set cnn = Nothing

End Function

-
Richard Ray
Jackson Hole Mountain Resort

 

[PWise]

how about

cnn.Open StrConn

 

[PWise]

GetLaborData = rst(0)

 

[RRinTetons]

Yep, no parens. Wrong language...

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

I've got this working:

Function GetLaborData(dCurr As Date, _
                        dPrev As Date, _
                        sCoNum As String, _
                        sDeptNum As String, _
                        sAcctNum As String) As Currency
                        

    Dim sConn As String
    Dim sSQL As String
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim strConn As String

    Set cnn = New ADODB.Connection
    strConn = "Provider=SQLNCLI10.1;"
    strConn = strConn + "Data Source=myServer;"
    strConn = strConn + "Initial Catalog=myDB;"
    strConn = strConn + "Integrated Security=SSPI;"
    strConn = strConn + "Auto Translate=False"
                
    cnn.Open strConn
'
    Set rst = New ADODB.Recordset

    sSQL = "SELECT SUM([vgp].[DebitAmount] - [vgp].[CreditAmount])"
    sSQL = sSQL & " FROM [dbo].[vwGLPostings01] AS vgp"
    sSQL = sSQL & " WHERE [vgp].[ACTNUMBR_1] = '" & sCoNum & "'"
    sSQL = sSQL & " AND [vgp].[ACTNUMBR_2] = '" & sDeptNum & "'"
    sSQL = sSQL & " AND [vgp].[ACTNUMBR_3] = '" & sAcctNum & "'"
    sSQL = sSQL & " AND [vgp].[TransactionDate] = '" & dCurr & "'"
    sSQL = sSQL & " GROUP BY [vgp].[TransactionDate]"

    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    On Error Resume Next

    rst.MoveFirst
    
    If Err.Number = 0 Then
        GetLaborData = rst(0)
    Else
        GetLaborData = 0
    End If
    
    rst.Close
    cnn.Close

    Set rst = Nothing
    Set cnn = Nothing

End Function

Now, how about if I want to call a T-SQL udf instead of building up the SQL statement as a text string? The function takes the same arguments as the SELECT statement uses in the WHERE clause and returns the same value. In a more mature version of this, though, it's likely to do a LOT more processing than I care to try to concatenate into a text var!

My first thought is to just build the function call as a text string, but since it's a function I don't know what the recordset woukld look like.

-
Richard Ray
Jackson Hole Mountain Resort

 

[PWise]

Create a Stored procedure

Create Procedure

Create Procedure SpGetLaborData 

@sCoNum varchar(25),
@sDeptNum Varchar(25),
@sAcctNum varchar(25),
@dCurr Datetime
As

    SELECT SUM([vgp].[DebitAmount] - [vgp].[CreditAmount]) as GetLaborData 
    FROM [dbo].[vwGLPostings01] AS vgp
    WHERE [vgp].[ACTNUMBR_1] = @sCoNum  
    AND [vgp].[ACTNUMBR_2] = @sDeptNum 
    AND [vgp].[ACTNUMBR_3] = @sAcctNum
    AND [vgp].[TransactionDate] =@dCurr
    GROUP BY [vgp].[TransactionDate]  


Return

Change function to

Set rst = New ADODB.Recordset
Ssql ="SpGetLaborData"
   rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, 4
[code]

 

[RRinTetons]

I guess I'm huing up on wanting it to be a function call (udf in SQL Server) rather than an sp. No reason I suppose, other than the fact that I wrote the udf first and started out with the goal of calling it as such. The sp will work, too.


-
Richard Ray
Jackson Hole Mountain Resort

 

[PWise]

create function dbo.UdfGetLaborData 
(@sCoNum varchar(25),
@sDeptNum Varchar(25),
@sAcctNum varchar(25),
@dCurr Datetime)
RETURNS int
As
Begin
Declare @GetLaborData Int    
SELECT @GetLaborData =SUM([vgp].[DebitAmount] - [vgp].[CreditAmount]) 
FROM [dbo].[vwGLPostings01] AS vgp    
WHERE [vgp].[ACTNUMBR_1] = @sCoNum      
AND [vgp].[ACTNUMBR_2] = @sDeptNum     
AND [vgp].[ACTNUMBR_3] = @sAcctNum    
AND [vgp].[TransactionDate] =@dCurr    
GROUP BY [vgp].[TransactionDate]  

Return @GetLaborData 

end

Set rst = New ADODB.Recordset
Ssql ="Select dbo.dbo.UdfGetLaborData(dCurr,sCoNum ,sDeptNum,sAcctNum)"   
rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, 1

 

[RRinTetons]

Perfect. I saw some examples that involved binding the args to an object and didn't know of the simple string concat would work.

Thanks.

-
Richard Ray
Jackson Hole Mountain Resort