Differences between stored procedures and custom functions in SQL

Stored procedures:

Stored procedures can make it much easier to manage the database and to display information about the database and its users. A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.

You can use stored procedures for any purpose that uses SQL statements, and it has the following advantages :

1. You can execute a series of SQL statements in a single stored procedure.
2. You can reference other stored procedures from within your own stored procedure, which simplifies a series of complex statements.

3. The stored procedure is compiled on the server when it is created, so it executes faster than a single SQL statement.


User-defined functions:

Microsoft SQL Server 2000 allows you to create user-defined functions. As with any function, a user-defined function is a routine that can return a value. Depending on the type of value returned, each user-defined function can be divided into the following three categories:

1. Returns a function that updates the data table: if the user-defined function contains a single SELECT statement and the statement can be updated, the table formatting results returned by the function can also be updated.

2. Returns a function that does not update the data table: If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, the result of the table format returned by the function is not updatable.

3. A function that returns a scalar value: A user-defined function can return a scalar value.


Stored Procedures

1. Powerful, less restrictive
2. Cannot directly reference the return value
3. Returning a Recordset with a SELECT statement

Custom functions

1. Many limitations, many statements can not be used, a lot of functions can not be implemented
2. Return values can be referenced directly
3. Returning a recordset with a table variable

Differences between stored procedures and custom functions in SQL