3

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

Moof
  • 31
  • 1
  • 5

2 Answers2

2

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.

Community
  • 1
  • 1
FistOfFury
  • 6,735
  • 7
  • 49
  • 57
0

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • I cannot then add parameters to it, though. It complains that `Parameters are not allowed in queries that cannot be displayed graphically` – Moof Jan 30 '12 at 18:04