I have created a user-defined function that takes one input parameter (varchar) and returns a table. It works fine if I just call this function with a hard-coded input value. Example: SELECT * FROM dbo.myTableFunction('ABC'). It would return all the records I expect.
However, I cannot pass to this function values from another table, it would not work. Example: SELECT dbo.myTableFunction(myTable.myCol) FROM myTable
I have used other user-defined functions (not returning table) that works just fine with passing in values from tables.
Any idea why the function returning table is failing?
Since the function is returning table, can I join on any of the columns it returns?
Thanks in advance.