SQL Server 中的函数与存储过程

Function vs. Stored Procedure in SQL Server

提问by Tarik

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?

有人能告诉我为什么吗？

采纳答案by MyItchyChin

Functions are computed values and cannot perform permanent environmental changes to SQL Server(i.e., no INSERTor UPDATEstatements allowed).

函数是计算值，不能执行永久环境更改SQL Server（即，不允许使用INSERT或UPDATE语句）。

A function can be used inline in SQLstatements 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-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.

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

How to call SQLfunction from store procedure and when we use a function instead of stored procedure.

如何SQL从存储过程调用 函数以及何时使用函数而不是存储过程。

Hi friends, today we will discuss When to use stored procedure and when to use the function. In simple team If you want to calculate some values and it's will return a single value, so it's not required:

嗨朋友们，今天我们将讨论什么时候使用存储过程，什么时候使用函数。在简单的团队中如果你想计算一些值并且它会返回一个值，所以它不是必需的：

https://programmingtechtutorial.blogspot.com/2020/01/when-use-storeprocedure-and-when-use.html

https://programmingtechtutorial.blogspot.com/2020/01/when-use-storeprocedure-and-when-use.html

回答by Bhaumik Patel

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

回答by Chris J

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 tableor SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

函数通常有一个输出和可选的输入。然后可以将输出用作另一个函数的输入（SQL Server 内置，例如 DATEDIFF、LEN 等）或用作 SQL 查询的谓词 - 例如，SELECT a, b, dbo.MyFunction(c) FROM table或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.

存储过程用于在事务中将 SQL 查询绑定在一起，并与外部世界进行交互。ADO.NET 等框架不能直接调用函数，但可以直接调用存储过程。

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

所以你需要小心编写函数。如果您从函数中的表中执行 SELECT，您需要问自己是否可以通过父存储过程中的 JOIN 或其他一些 SQL 构造（例如 CASE ... WHEN ... ELSE ...结尾）。

回答by Kumar Manish

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.

• Select 语句中不能使用存储过程。
• 存储过程支持延迟名称解析。
• 存储过程通常用于执行业务逻辑。
• 存储过程可以返回任何数据类型。
• 与用户定义的函数相比，存储过程可以接受更多数量的输入参数。存储过程最多可以有 21,000 个输入参数。
• 存储过程可以执行动态 SQL。
• 存储过程支持错误处理。
• 非确定性函数可用于存储过程。

---

---

• 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. RAISEERROROR @@ERRORare not allowed in UDFs.
• Non-deterministic functions cannot be used in UDFs. For example, GETDATE()cannot be used in UDFs.

• 用户定义的函数可以在 Select 语句中使用。
• 用户定义的函数不支持延迟名称解析。
• 用户定义的函数通常用于计算。
• 用户定义的函数应该返回一个值。
• 用户定义的函数不能返回图像。
• 用户定义的函数接受的输入参数数量少于存储过程。UDF 最多可以有 1,023 个输入参数。
• 不能在用户定义的函数中使用临时表。
• 用户定义的函数不能执行动态 SQL。
• 用户定义的函数不支持错误处理。 在 UDF 中不允许使用RAISEERROROR @@ERROR。
• 不能在 UDF 中使用非确定性函数。例如，GETDATE()不能在 UDF 中使用。

回答by Alex Martelli

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 语句分组。毕竟，这是两个完全不同的用例！

回答by Aakash Singh

              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
                                       |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                       |   statements.

回答by Ankit

Basic Difference

基本区别

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

函数必须返回一个值，但在存储过程中它是可选的（过程可以返回零或 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.

过程允许 SELECT 以及其中的 DML(INSERT/UPDATE/DELETE) 语句，而 Function 只允许其中的 SELECT 语句。

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/HAVING/SELECT 部分的任何地方的 SQL 语句中使用，而函数可以。

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

返回表的函数可以被视为另一个行集。这可以在与其他表的 JOIN 中使用。

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

内联函数可以看作是带参数的视图，可以在 JOIN 和其他行集操作中使用。

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

来源

回答by OpenSource

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 server 程序员可用的重要工具。您可以像这样在 SQL 语句中内联使用它

SELECT a, lookupValue(b), c FROM customers 

where lookupValuewill 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:

lookupValueUDF在哪里。使用存储过程时，这种功能是不可能的。同时，您不能在 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 最重要的用法。

回答by Tigerjz32

Stored Proceduresare used as scripts. They run a series of commands for you and you can schedule them to run at certain times. Usually runs multiples DML statements like INSERT, UPDATE, DELETE, etc. or even SELECT.

存储过程用作脚本。它们为您运行一系列命令，您可以安排它们在特定时间运行。通常运行多个 DML 语句，如 INSERT、UPDATE、DELETE 等，甚至是 SELECT。

Functionsare used as methods.You pass it something and it returns a result. Should be small and fast - does it on the fly. Usually used in a SELECT statement.

函数用作方法。你传递了一些东西，它返回一个结果。应该小而快 - 即时完成。通常用于 SELECT 语句。

回答by JaiSankarN

Stored procedure:

存储过程：

• Is like a miniature program in 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.
• (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 EXECor EXECUTEstatement.
• Returns table variables, but we can't use OUTparameter.
• Supports transactions.

• 就像 SQL Server 中的一个微型程序。
• 可以像选择语句一样简单，也可以像从数据库中的多个表中添加、删除、更新和/或读取数据的长脚本一样复杂。
• （可以实现循环和游标，它们都允许您处理较小的结果或对数据进行逐行操作。）
• 应调用 usingEXEC或EXECUTE语句。
• 返回表变量，但我们不能使用OUT参数。
• 支持交易。

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:

  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 JOINclauses in your functions. If your function has a JOINclause and you call it from another select statement that returns multiple results, that function call will JOINthose tables together for eachline 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.

• Returns the values using OUTparameter.
• Does not support transactions.

• 不能用于更新、删除或向数据库添加记录。
• 简单地返回单个值或表值。

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

  SELECT dbo.functionname('Parameter1')
  

  或者

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

• 对于简单的可重用选择操作，函数可以简化代码。请注意JOIN在函数中使用子句。如果您的函数有一个JOIN子句，并且您从另一个返回多个结果的 select 语句中调用它，那么该函数调用会将JOIN这些表一起用于结果集中返回的每一行。因此，尽管它们有助于简化某些逻辑，但如果使用不当，它们也可能成为性能瓶颈。

• 使用OUT参数返回值。
• 不支持事务。