asked
Passing a non-hard-coded parameter value to a function
Hello - here's my somewhat generic (and, I suspect, poorly phrased) question. I built a function which will return the preferred address for the id of the entity passed to it. It's a user-defined table function (returning the address columns). Is it possible to use a passed parameter to have it build the table based only on the ids within the outer query? I don't see how this is possible, but it seems logical that this would improve performance because the function wouldn't be building the preferred address table for every entity in the db who has one. Hope this makes sense...it's a "best practices" type of question rather than something concrete, but I am interested in how that could work - how you'd pass the current id into the function and have it insert the row, then get the next one, etc. (sounds more like a cursor, actually)...thoughts? Thank you -- Donna
I'm not sure I'm understanding what you really want to achieve. Can you elaborate a bit more, perhaps post some pseudo code of how you think the solution would look if it worked?
I'm sure I'm misunderstanding what you're saying, so please take that into account when I say this. From what you described, no, you can't do that. A table-valued user defined function must have the table defined up front. It can't be dynamically determined on the fly. Also, I'm not sure, but it sounds like you probably should not be using a UDF to query the data. I suspect you'd get more mileage (as well as better performance) out of a derived table (a "tabled" defined by a select statement within a query and referred to and used as a table in the query).
If the function you built is an "Inline" Table valued function, then it would be **very** effective to use it in an INSERT/SELECT statement along with a CROSS APPLY.
