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 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).