Stored procedure with output parameters vs. table-valued function?

Question

Which approach is better to use if I need a member (sp or func) returning 2 parameters:

CREATE PROCEDURE Test
   @in INT,
   @outID INT OUT,
   @amount DECIMAL OUT
AS
BEGIN
   ...
END

or

CREATE FUNCTION Test
(
   @in INT
)
RETURNS @ret TABLE (outID INT, amount DECIMAL)
AS
BEGIN
   ...
END

What are pros and cons of each approach considering that the result will passed to another stored procedure:

EXEC Foobar @outID, @outAmount

Answer 1

Hey,

A stored procedure that calls a function :-) I think either will suite you... if your app uses stored procedures for querying the database, then it may be best to be consistent... if you use an ORM, it may not recognize the function... I don't think you can go wrong with either.

In one of my apps, we preferred using the function approach, to throw in another perspective.

HTH.

Answer 2

I think your better bet would be the SP because with the TBF (table value function) you'd have to iterate through the table to get your value.

Bear in mind that if you iterate through the table in SQL, then you'll need to use a CURSOR (which aren't too bad, but can be a little tricky to use).

Answer 3

With the stored procedure using output parameters you will only be able to return the two values: @outID and @amount.

With the table-valued function, you will be able to return a whole set of (outID, amount) tuples. In addition, a table-valued function can be used wherever table or view expressions are allowed in queries, such as:

SELECT dbo.Test(1) AS TestValues

Answer 4

I would argue The output parameter approach is most desirable. This makes it more self documenting that not more than one tuple is expected and I would assume is likely to be more efficient.

Answer 5

A table valued function can only be used within a scope of a single SELECT statement. It cannot perform DML, catch exceptions etc.

On the other hand, it can return a set which can immediate be joined with another recordset in the same query.

If you use DML or don't need to use the output parameters in the set-based statements, use a stored proc; otherwise create a TVF.

Answer 6

I would only use a table-valued function if I needed to obtain a table of values.

If there is only one "row" in your output then it would be preferable to use output parameters in a Stored Procedure.

One exception to this is if your SP/UDF can be written as a single SELECT statement - i.e. an Inline Function - because SQL Server can make better optimizations if you ever need to do something like join it to the output of another query. You may not be doing that now, but writing an inline UDF means you won't be caught off-guard with slow-as-molasses queries and timeout reports if somebody starts using it that way in the future.

If none of that applies to you then I would use a Stored Procedure for the reasons outlined; you don't want to create the illusion of set-based semantics when you aren't actually supporting them.

Answer 7

Output parameters.

Multi-statement table value functions are difficult to trace and tune. Stick with the stored procedure which is easier to troubleshoot.

Also, you are limited to what you can do in a udf. Say you need to add logging, or call an extended stored proc later... you can't use a udf for this.