Function vs. Stored Procedure in SQL Server

The difference between SP and UDF is listed below:

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).

Differences between stored procedures and user-defined functions:

• Stored procedures cannot be used in Select statements.
• Stored procedures support Deferred Name Resolution.
• Stored procedures are generally used for performing business logic.
• Stored procedures can return any datatype.
• Stored procedures can accept greater numbers of input parameter than user defined functions. Stored procedures can have up to 21,000 input parameters.
• Stored procedures can execute Dynamic SQL.
• Stored procedures support error handling.
• Non-deterministic functions can be used in stored procedures.

---

• User-defined functions can be used in Select statements.
• User-defined functions do not support Deferred Name Resolution.
• User-defined functions are generally used for computations.
• User-defined functions should return a value.
• User-defined functions cannot return Images.
• User-defined functions accept smaller numbers of input parameters than stored procedures. UDFs can have up to 1,023 input parameters.
• Temporary tables cannot be used in user-defined functions.
• User-defined functions cannot execute Dynamic SQL.
• User-defined functions do not support error handling. RAISEERROR OR @@ERROR are not allowed in UDFs.
• Non-deterministic functions cannot be used in UDFs. For example, GETDATE() cannot be used in UDFs.