When to Use Functions over Stored Procedures

Question

What advantages/circumstances do functions have over stored procedures? Want to justify use for function. It seems stored procedures benefit in every way, from transaction handling, input, multiple values, with insert into...exec. So when are ideal situations to apply functions rather than sps? This is not an opinion based question or recommendation, or requesting to use sps all the time, just want to see benefits of functions.

I see Number 5 is function advantages, but there are many ways around this in sprocs.

Answer 1

from a quick search i found the following article

as to the benefits of a udf vs an sps:

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

you can use this to limit the use of a query to a select statment only via the function

Procedures can't be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

this might have an effect on the query load because for each row returned it will execute your udf.

Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

In my opinion, it's really a matter of the relevant use case. You can use a function when you need to manipulate the data per row, or when you want to be on the safe side that a DML query won't run for a user that has permissions to run DML queries.