question

DonnaB avatar image
DonnaB 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
parametersbest-practicefunctionudf
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.