Dynamic Filter, Inline Table & HOST_NAME() - Does it work or MS BUG?

Hi All,
Can someone give me a definitive answer on the following:
Can I define a UDF that is used by my Dynamic Filter, receives the HOST_NAME() as a
parameter and passes back a table that I can perform a SELECT IN on ? An example is
shown below...
SELECT <published_columns> FROM [dbo].[Stores] WHERE StoreID
IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME()))
This Dynamic Filter gives me an error
Error 170, line 1: Incorrect Syntax near '('.
I've had conflicting views on this, and I'd really like to get it sorted, any
input is welcome.
Cube,
I have only been able to use UDFs before when they are scalar, eg
SELECT <published_columns> FROM [dbo].[Region] WHERE region.regionid = dbo.fn_TaxRate(host_name())
works fine. In your case can you restructure your query to return a scalar value and then use this value combined with another table in a join + where clause perhaps?
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the reply. Not a bad idea. In relation to my question about passing in
HOST_NAME() into my inline-table UDF is it the case that this isn't possible ?
BTW - I'll give you all an update when I've tried the other suggestion...now :o)
Cheers
|||Cube,
I can't say definitively that it's not possible - I have always had the same
error message you've seen, and probably like yourself I've tried every
syntax combination for use of the table-leveled UDF I could think of. I've
never heard of anyone else using a table valued UDF in a dynamic filter, but
hopefully if someone else has they'll post it up.
Regards,
Paul Ibison