norbert.manyi (10/2/2012)
Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?
Example:
Employees can belong to regions and/or teams. Teams are split into organizations.
There is more than one type of team-leader. One that is directly above the employees, one that is in charge of a region and one that is in charge of an organization.
Most reports can be run by either type of team-leader for their employees, and they all do filtering at the beginning. (this is how it was before we took over)
Although this can also be done using multiple views and a few lines of code, I think this is getting closer to the 'cannot use views' scenario.
If you can't code it up as a view, then it's unlikely that you can code it up as a query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden