Function vs. Stored Procedure in SQL Server

2018-06-08 03:38:32

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that.

Can some one tell me why?

Functions are computed values and cannot perform permanent environmental changes to SQL Server (ie 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-sciency 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.

SP和UDF的区别如下：

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (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        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

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

链接地址: http://www.djcxy.com/p/24744.html

上一篇: 如何在存储过程sql server 2005中使用循环

下一篇: 函数与SQL Server中的存储过程