sql - Functions vs Stored Procedures

Question

Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use?

In short, what I want to know is:

Which are the main differences between functions and stored procedures? What considerations do I have to take into account for using one or the other?

Answer 1

If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.

Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.

So use whichever one minimally allows you to express your desired result.