SQL Server中的函数和存储过程

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?

有人能告诉我为什么吗?

16 个解决方案

#1

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

函数是计算值，不能对SQL Server执行永久的环境更改(即不允许插入或更新语句)。

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.

如果函数返回标量值，可以在SQL语句中使用内联函数;如果函数返回结果集，则可以使用内联函数。

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

一个值得注意的观点，总结了答案。感谢@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.

函数遵循计算机科学定义，因为它们必须返回一个值，并且不能更改作为参数接收的数据(参数)。函数不允许更改任何内容，必须至少有一个参数，并且必须返回一个值。存储的procs不必有参数，可以更改数据库对象，也不必返回值。

#2

The difference between SP and UDF is listed below:

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

#3

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.

函数和存储过程有各自的用途。虽然这不是最好的类比，但是函数可以被看作是任何编程语言中使用的任何其他函数，但是存储的proc更像是单独的程序或批处理脚本。

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

函数通常有一个输出和可选的输入。然后可以将输出用作另一个函数的输入(如DATEDIFF、LEN等内置的SQL服务器)或SQL查询的谓词——例如，从表中选择a、b、dbo.MyFunction(c)或从表中选择a、b、c，其中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.

存储的procs用于将SQL查询绑定到事务中，并与外部世界进行交互。框架如ADO。NET等不能直接调用函数，但可以直接调用存储的proc。

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:

MyFunction被声明为:

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.

这里发生的是，对表MyTable中的每一行调用函数MyFunction。如果MyTable有1000行，那么这是对数据库的另外1000个特别查询。类似地，如果在列规范中指定时调用该函数，那么将为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).

所以写函数时要小心。如果您确实从函数中的一个表中进行选择，那么您需要问问自己，是否可以使用父存储的proc中的连接或其他SQL构造(例如CASE…)更好地执行它。当……其他的……结束)。

#4

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!

当您想要计算并返回一个值以便在其他SQL语句中使用时，编写一个用户定义的函数;相反，在需要的时候编写一个存储过程是对一组可能复杂的SQL语句进行分组。毕竟，这是两个非常不同的用例!

#5

Differences between stored procedures and user-defined functions:

存储过程和用户定义函数之间的差异:

• Stored procedures cannot be used in Select statements.
• 不能在Select语句中使用存储过程。
• 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.
• 存储过程可以接受比用户定义的函数更多的输入参数。存储过程可以有多达21,000个输入参数。
• Stored procedures can execute Dynamic SQL.
• 存储过程可以执行动态SQL。
• Stored procedures support error handling.
• 存储过程支持错误处理。
• Non-deterministic functions can be used in stored procedures.
• 非确定性函数可以在存储过程中使用。

---

• User-defined functions can be used in Select statements.
• 用户定义的函数可以在Select语句中使用。
• 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.
• 与存储过程相比，用户定义函数接受的输入参数数量更少。udf可以有多达1023个输入参数。
• Temporary tables cannot be used in user-defined functions.
• 在用户定义的函数中不能使用临时表。
• User-defined functions cannot execute Dynamic SQL.
• 用户定义函数不能执行动态SQL。
• User-defined functions do not support error handling. RAISEERROR OR @@ERROR are not allowed in UDFs.
• 用户定义的函数不支持错误处理。在udf中不允许使用RAISEERROR或@@ERROR。
• Non-deterministic functions cannot be used in UDFs. For example, GETDATE() cannot be used in UDFs.
• 在udf中不能使用非确定性函数。例如，在UDFs中不能使用GETDATE()。

#6

Basic Difference

基本区别

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

函数必须返回值，但在存储过程中它是可选的(过程可以返回0或n值)。

Functions can have only input parameters for it whereas Procedures can have input/output parameters .

函数只能有输入参数，而过程可以有输入/输出参数。

Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

函数接受一个输入参数，这是强制性的，但是存储过程可能会将o输入到n个输入参数中。

Functions can be called from Procedure whereas Procedures cannot be called from Function.

函数可以从过程调用，而过程不能从函数调用。

Advance Difference

之前的区别

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

过程允许在其中选择和DML(插入/更新/删除)语句，而函数只允许在其中选择语句。

Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

过程不能在SELECT语句中使用，而函数可以嵌入到SELECT语句中。

Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

存储过程不能在WHERE/ have /SELECT部分的SQL语句中使用，而函数可以是。

Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

返回表的函数可以视为另一个行集。这可以用于与其他表的连接。

Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

内联函数可以作为获取参数的视图，也可以用于连接和其他行集操作。

Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

过程中的try-catch块可以处理异常，而函数中不能使用try-catch块。

We can go for Transaction Management in Procedure whereas we can't go in Function.

我们可以在程序上进行交易管理，但不能在功能上进行。

source

源

#7

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

用户定义函数是sql服务器程序员可用的重要工具。您可以在这样的SQL语句中内联地使用它

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:

查找值将是UDF。使用存储过程时不可能实现这种功能。同时，你不能在UDF中做某些事情。这里要记住的基本内容是UDF:

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

对我来说，UDF的内联用法是UDF最重要的用法。

#8

Stored Procedures are used as scripts. They run series of commands for you and you can schedule them to run at certain times.

存储过程被用作脚本。它们为您运行一系列命令，您可以安排它们在特定的时间运行。

Functions are used as methods. You pass it something and it returns a result. Should be small and fast - does it on the fly.

函数用作方法。你传递一些东西，它会返回一个结果。应该是又小又快——在飞行中完成。

#9

Stored procedure:

存储过程:

• Is like a miniature program in SQL Server.
• 就像SQL Server中的一个微型程序。
• Can be as simple as a select statement, or as complex as a long script that adds, deletes, updates, and/or reads data from multiple tables in a database.
• 可以像select语句一样简单，也可以像添加、删除、更新和/或从数据库中的多个表读取数据的长脚本一样复杂。
• (Can implement loops and cursors, which both allow you to work with smaller results or row by row operations on data.)
• (可以实现循环和游标，它们都允许您对数据进行更小的结果或逐行操作。)
• Should be called using EXEC or EXECUTE statement.
• 应该使用EXEC或EXECUTE语句调用。
• Returns table variables, but we can't use OUT parameter.
• 返回表变量，但不能使用OUT参数。
• Supports transactions.
• 支持事务。

Function:

功能:

• Can not be used to update, delete, or add records to the database.
• 不能用于更新、删除或向数据库添加记录。
• Simply returns a single value or a table value.
• 简单地返回单个值或表值。

• Can only be used to select records. However, it can be called very easily from within standard SQL, such as:

  只能用于选择记录。但是，可以很容易地从标准SQL中调用它，比如:

  SELECT dbo.functionname('Parameter1')
  

  or

  或

  SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  

• For simple reusable select operations, functions can simplify code. Just be wary of using JOIN clauses in your functions. If your function has a JOIN clause and you call it from another select statement that returns multiple results, that function call will JOIN those tables together for each line returned in the result set. So though they can be helpful in simplifying some logic, they can also be a performance bottleneck if they're not used properly.

  对于简单的可重用选择操作，函数可以简化代码。只需要注意在函数中使用连接子句。如果你的函数有加入条款,你叫它从另一个select语句返回多个结果,这些表函数调用将加入在一起每一行返回的结果集。所以尽管他们可以帮助简化一些逻辑,他们也可以成为一个性能瓶颈,如果他们没有正确使用。

• Returns the values using OUT parameter.
• 使用OUT参数返回值。
• Does not support transactions.
• 不支持事务。

#10

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

干杯

#11

SQL Server functions, like cursors, are meant to be used as your last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. Talking about performance is talking about a table with more than 1,000,000 records hosted on a server on a middle-class hardware; otherwise you don't need to worry about the performance hit caused by the functions.

像游标一样，SQL Server的功能将被用作最后的武器!它们确实存在性能问题，因此应该尽可能避免使用表值函数。谈论性能是指在中等硬件上的服务器上托管超过1,000,000条记录的表;否则，您不需要担心函数造成的性能损失。

1. Never use a function to return a result-set to an external code (like ADO.Net)
2. 永远不要使用函数将结果集返回到外部代码(如ADO.Net)
3. Use views/stored procs combination as much as possible. you can recover from future grow-performance issues using the suggestions DTA (Database Tuning Adviser) would give you (like indexed views and statistics) --sometimes!
4. 尽可能多地使用视图/存储的procs组合。您可以使用DTA(数据库调优顾问)提供的建议(如索引视图和统计数据)从未来的增长性能问题中恢复过来——有时是这样的!

for further reference see: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

进一步参考:http://databases.aspfaq.com/database/should-i-use-a-view-a-store -procedure-or- user- defin- function.html

#12

Start with functions that return a single value. The nice thing is you can put frequently used code into a function and return them as a column in a result set.

从返回单个值的函数开始。好处是，您可以将经常使用的代码放入函数中，并将它们作为结果集中的一列返回。

Then, you might use a function for a parameterized list of cities. dbo.GetCitiesIn("NY") That returns a table that can be used as a join.

然后，您可以对一个参数化的城市列表使用一个函数。getcitiesin(“NY”)返回可以用作连接的表。

It's a way of organizing code. Knowing when something is reusable and when it is a waste of time is something only gained through trial and error and experience.

这是一种组织代码的方式。知道什么东西什么时候是可重用的，什么时候是浪费时间，只有通过尝试、错误和经验才能获得。

Also, functions are a good idea in SQL Server. They are faster and can be quite powerful. Inline and direct selects. Careful not to overuse.

在SQL Server中，函数也是一个好主意。它们速度更快，而且可以非常强大。内联和直接选择。注意不要过度使用。

#13

• It is mandatory for Function to return a value while it is not for stored procedure.
• 函数必须返回一个值，而不是存储过程。
• Select statements only accepted in UDF while DML statements not required.
• 选择UDF中只接受的语句，而不需要DML语句。
• Stored procedure accepts any statements as well as DML statements.
• 存储过程接受任何语句以及DML语句。
• UDF only allows inputs and not outputs.
• UDF只允许输入而不允许输出。
• Stored procedure allows for both inputs and outputs.
• 存储过程允许输入和输出。
• Catch blocks cannot be used in UDF but can be used in stored procedure.
• 不能在UDF中使用Catch块，但可以在存储过程中使用。
• No transactions allowed in functions in UDF but in stored procedure they are allowed.
• 在UDF函数中不允许有事务，但是在存储过程中允许有事务。
• Only table variables can be used in UDF and not temporary tables.
• UDF中只能使用表变量，而不能使用临时表。
• Stored procedure allows for both table variables and temporary tables.
• 存储过程支持表变量和临时表。
• UDF does not allow stored procedures to be called from functions while stored procedures allow calling of functions.
• UDF不允许从函数调用存储过程，而存储过程允许调用函数。
• UDF is used in join clause while stored procedures cannot be used in join clause.
• UDF用于join子句，而存储过程不能用于join子句。
• Stored procedure will always allow for return to zero. UDF, on the contrary, has values that must come - back to a predetermined point.
• 存储过程总是允许返回0。相反，UDF的值必须回到预定的点。

#14

• Functions can be used in a select statement where as procedures cannot.

  函数可以在select语句中使用，而过程不能。

• Stored procedure takes both input and output parameters but Functions takes only input parameters.

  存储过程接受输入和输出参数，但函数只接受输入参数。

• Functions cannot return values of type text, ntext, image & timestamps where as procedures can.

  函数不能返回类型为text、ntext、image和timestamp的值。

• Functions can be used as user defined datatypes in create table but procedures cannot.

  在create table中，函数可以用作用户定义的数据类型，但是过程不能。

***Eg:-create table <tablename>(name varchar(10),salary getsal(name))

***Eg:-创建表 (name varchar(10)，salary getsal(name))

Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get’s executed and the return Type is returned as the result set.

这里getsal是一个用户定义的函数,返回一个工资类型,在创建表时不分配存储对于工资类型,和getsal功能也不执行,但是当我们从这个表抓取一些值,getsal函数得到的执行和返回类型是作为结果集返回。

#15

Here's a practical reason to prefer functions over stored procedures. If you have a stored procedure that needs the results of another stored procedure, you have to use an insert-exec statement. This means that you have to create a temp table and use an exec statement to insert the results of the stored procedure into the temp table. It's messy. One problem with this is that insert-execs cannot be nested.

这里有一个实际的原因，使我们更喜欢函数而不是存储过程。如果您有一个存储过程需要另一个存储过程的结果，那么您必须使用insert-exec语句。这意味着您必须创建一个临时表，并使用exec语句将存储过程的结果插入到临时表中。它是混乱的。这样做的一个问题是插入-执行不能嵌套。

If you're stuck with stored procedures that call other stored procedures, you may run into this. If the nested stored procedure simply returns a dataset, it can be replaced with a table-valued function and you'll no longer get this error.

如果您遇到了调用其他存储过程的存储过程，您可能会遇到这种情况。如果嵌套存储过程只是返回一个数据集，那么可以用表值函数替换它，您将不再得到这个错误。

(this is yet another reason we should keep business logic out of the database)

(这也是我们应该将业务逻辑排除在数据库之外的另一个原因)

#16

In SQL Server, functions and stored procedure are two different types of entities.

在SQL Server中，函数和存储过程是两种不同类型的实体。

Function: In SQL Server database, the functions are used to perform some actions and the action returns a result immediately. Functions are two types:

函数:在SQL Server数据库中，函数用于执行一些操作，操作立即返回一个结果。函数是两种类型:

1. System defined

   系统定义的

2. User defined

   用户定义的

Stored Procedures: In SQL Server, the stored procedures are stored in server and it can be return zero, single and multiple values. Stored Procedures are two types:

存储过程:在SQL Server中，存储过程存储在服务器中，可以返回零、单个和多个值。存储过程有两种类型:

1. System Stored Procedures
2. 系统存储过程
3. User Defined Procedures
4. 用户定义的过程