I'd like to factor out some of the functionality in our SPs into separate functions/stored procedures, but I'm worried about the potential performance penalty. In particular, how efficient will be a join with a table-valued function, as opposed to writing out the query from that function in full?
An in-line table valued function (TVF) is simply a macro that expands into the containing query: no more, no less. Just like a standard view.
A multi-statement TVF on the other hand has to run to completion and won't be optimised for subsequent operations on the results