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.
The difference between SP and UDF is listed below:
| Stored Procedure (SP) | Function (UDF - User Defined) |
|---|---|
| SP can return zero, single or multiple values. | Function must return a single value (which may be a scalar or a table). |
| We can use transaction in SP. | We can't use transaction in UDF. |
| SP can have input/output parameter. | Only input parameter. |
| We can call function from SP. | We can't call SP from function. |
| We can't use SP in SELECT/ WHERE/ HAVING statement. | We can use UDF in SELECT/ WHERE/ HAVING statement. |
| We can use exception handling using Try-Catch block in SP. | We can't use Try-Catch block in UDF. |
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 INTEGERASBEGIN DECLARE @retval INTEGER SELECT localValue FROM dbo.localToNationalMapTable WHERE nationalValue = @someValue RETURN @retvalENDWhat 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).