When is it appropriate to use stored procs vs table valued functions

Question

We are currently using Stored procs for most of our reporting, but I would like to have the flexibility of being able to join results sets with further criteria.

To do this with stored procs, I need to store the resultset from the storedproc in a temp tables as illustrated below:

CREATE TABLE #Top_1000_Customers (
  CustomerCode BIGINT, 
  Firstname VARCHAR(100),
  Surname VARCHAR(100),
  State VARCHAR(),
  MonthlySpend FLOAT)

INSERT INTO #Top_1000_Customers
EXEC FindTop1000Customers()  

SELECT CustomerCode, Firstname, Surname, State, MonthlySpend float
FROM #Top_1000_Customers
WHERE State = 'VIC'  

DROP TABLE #Top_1000_Customers

If I do this using a table valued function this code looks like:

SELECT FindTop1000Customers()
WHERE State='VIC'

If I want to I can even join the table valued function with another table.

This seems to be a bit more elegant than using stored procs and also looks like it would perform better - as it does not have to spool results to a temp table.

Are there any significant reasons why I would be better off using stored procs to accomplish these types of tasks instead of using table valued functions?

Answer 1

There are restrictions on functions in general that do not apply to stored procedures. See User-Defined Function Design Guidelines.

Answer 2

An inline table valued function is equivalent to a parametrized view, and they can be much preferred as a method of enforcing database logic without incurring much penalty because the optimizer can expand them inline just like a view. Inline TVFs have to have all the logic inline obviously, so there are limits to the things you can do.

An multi-line table-valued function, on the other hand, has more flexibility, but the features of it vs. a stored proc might have to be weighed carefully.

In your case, if you can do it with a regular view or an inline TVF, I would do that first.

Answer 3

Can your FindTop1000Customers procedure be expressed in a single SELECT statement? If so, an in-line table valued function might be a good substitute. The State='VIC' criteria will be pushed down to the base tables and the query may execute much faster.

If not, a multi-statement table valued function will still need to materialize the full result set before applying the WHERE clause. To push the filter down to the base tables you would need to parameterize the function, just like you would a stored proc. And you would lose a lot programming power (temporary tables, dynamic SQL, etc).

I suggest Erland Sommarskog's articles on How to Share Data Between Stored Procedures, Dynamic Search Conditions, and Dynamic SQL for more ideas on the subject.