I have created an Inline Table-Value User Defined Function in SQL Server
I would like to input the parameters in various cells in an Excel spreadsheet, and have a table that will update with the results from those parameters when I enter them.
Normally, I do this with Microsoft Query, but that doesn't allow me to call a User Defined Function. So how do I go about creating an odc file, or otherwise connecting to that function?
I am using Excel 2010 and SQL Server 2008
I'm having this same problem. You need to use VBA and ADO to call the function with parameters via a SQL query.
This link shows how to make the call for a scalar function.
A table value function would be similar, but you'd have to return the data as an array/matrix to call it directly from an Excel cell. Alternatively you can follow the directions here to use .CopyFromREcordset() and put the results into a worksheet.
Have you tried:
select dbo.YourFunction()
for a scalar UDF, or:
select * from dbo.YourFunction()
for a table-valued UDF? MS Query might complain it cannot display the query graphically, but you should be able to enter it in SQL mode.
Parameters are not allowed in queries that cannot be displayed graphically