When to create a function rather than a procedure

Question

I have many stored procedures that are in production but no functions.

Here in MSDN is the definition of CREATE FUNCTION. It says the following:

Creates a user-defined function in SQL Server 2012. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways:

My stored procedures already seem to achieve all of the above.

What is a definite situation when one chooses a function rather than a stored procedure?

Martin Smith · Accepted Answer · 2013-03-26 08:05:31Z

4

When you want to use the result integrated as part of a larger query.

e.g. you can join or cross apply table valued functions and you can evaluate a Scalar UDF for each row in a result (with care as this can have performance implications).

For stored procedures you would need to execute them and capture the result into a temporary table etc to be able to join on it in a wider query and would require cursors or similar to simulate the Scalar UDF behaviour and execute it for each row.

edited Mar 26, 2013 at 8:05

answered Mar 26, 2013 at 7:57

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

When to create a function rather than a procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related