Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Functions and stored procedures serve separate purposes. Although it's not the best analogy, functions can be viewed literally as any other function you'd use in any programming language, but stored procs are more like individual programs or a batch script.

Functions normally have an output and optionally inputs. The output can then be used as the input to another function (a SQL Server built-in such as DATEDIFF, LEN, etc) or as a predicate to a SQL Query - e.g., SELECT a, b, dbo.MyFunction(c) FROM table or SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Stored procs are used to bind SQL queries together in a transaction, and interface with the outside world. Frameworks such as ADO.NET, etc. can't call a function directly, but they can call a stored proc directly.

Functions do have a hidden danger though: they can be misused and cause rather nasty performance issues: consider this query:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Where MyFunction is declared as:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

What happens here is that the function MyFunction is called for every row in the table MyTable. If MyTable has 1000 rows, then that's another 1000 ad-hoc queries against the database. Similarly, if the function is called when specified in the column spec, then the function will be called for each row returned by the SELECT.

So you do need to be careful writing functions. If you do SELECT from a table in a function, you need to ask yourself whether it can be better performed with a JOIN in the parent stored proc or some other SQL construct (such as CASE ... WHEN ... ELSE ... END).

Write a user-defined function when you want to compute and return a value for use in other SQL statements; write a stored procedure when you want instead is to group a possibly-complex set of SQL statements. These are two pretty different use cases, after all!

a User Defined Function is an important tool available to a sql server programmer. You can use it inline in a SQL statement like so

SELECT a, lookupValue(b), c FROM customers 

where lookupValue will be an UDF. This kind of functionality is not possible when using a stored procedure. At the same time you cannot do certain things inside a UDF. The basic thing to remember here is that UDF's:

• cannot create permanent changes
• cannot change data

a stored procedure can do those things.

For me the inline usage of a UDF is the most important usage of a UDF.

To decide on when to use what the following points might help-

1. Stored procedures can't return a table variable where as function can do that.

2. You can use stored procedures to alter the server environment parameters where as using functions you can't.

cheers

I realize this is a very old question, but I don't see one crucial aspect mentioned in any of the answers: inlining into query plan.

Functions can be...

1. Scalar:

   CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END

2. Multi-statement table-valued:

   CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END

3. Inline table-valued:

   CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

The third kind (inline table-valued) are treated by the query optimizer essentially as (parametrized) views, which means that referencing the function from your query is similar to copy-pasting the function's SQL body (without actually copy-pasting), leading to the following benefits:

• The query planner can optimize the inline function's execution just as it would any other sub-query (e.g. eliminate unused columns, push predicates down, pick different JOIN strategies etc.).
• Combining several inline function doesn't require materializing the result from the first one before feeding it to the next.

The above can lead to potentially significant performance savings, especially when combining multiple levels of functions.

NOTE: Looks like SQL Server 2019 will introduce some form of scalar function inlining as well.

Generally using stored procedures is better for perfomances. For example in previous versions of SQL Server if you put the function in JOIN condition the cardinality estimate is 1 (before SQL 2012) and 100 (after SQL 2012 and before of SQL 2017) and the engine can generate a bad execution plan.

Also if you put it in WHERE clause the SQL Engine can generate a bad execution plan.

With SQL 2017 Microsoft introduced the feature called interleaved execution in order to produce a more accurate estimate but the stored procedure remains the best solution.

For more details look the following article of Joe Sack https://techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417