SQL Server 中的函数与存储过程

海未深 2024-08-05 17:43:24

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

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

评论中值得注意的一点，总结了答案。感谢@Sean K Anderson：

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

回复收藏 0 原文

空城仅有旧梦在 2024-08-05 17:43:24

下表总结了这些差异：

	存储过程	函数
返回	零个或多个值	单个值（可以是标量或表）
可以使用事务吗？	是	否
可以输出到参数吗？	是	否
可以互相打电话吗？	可以调用函数	不能调用存储过程
可在 SELECT、WHERE 和 HAVING 语句中使用吗？	否	是
支持异常处理（通过 try/catch）？	是	否

Here's a table summarizing the differences:

	Stored Procedure	Function
Returns	Zero or more values	A single value (which may be a scalar or a table)
Can use transaction?	Yes	No
Can output to parameters?	Yes	No
Can call each other?	Can call a function	Cannot call a stored procedure
Usable in SELECT, WHERE and HAVING statements?	No	Yes
Supports exception handling (via try/catch)?	Yes	No

回复收藏 0 原文

素年丶 2024-08-05 17:43:24

函数和存储过程有不同的用途。尽管这不是最好的类比，但函数可以从字面上看为您在任何编程语言中使用的任何其他函数，但存储过程更像是单个程序或批处理脚本。

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

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

但函数确实有一个隐藏的危险：它们可能被误用并导致相当严重的性能问题：考虑这个查询：

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

MyFunction 声明为：

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

这里发生的情况是，为表 MyTable 中的每一行调用函数 MyFunction。如果 MyTable 有 1000 行，那么这就是针对数据库的另外 1000 个临时查询。同样，如果在列规范中指定时调用该函数，则将为 SELECT 返回的每一行调用该函数。

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

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

回复收藏 0 原文

哥，最终变帅啦 2024-08-05 17:43:24

存储过程和用户定义函数的区别：

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

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

回复收藏 0 原文

樱桃奶球 2024-08-05 17:43:24

存储过程	函数（用户定义函数）
过程可以返回 0、单个或多个值	函数只能返回单个值
过程可以有输入、输出参数	函数只能有输入参数
过程不能从函数调用	函数可以从过程
调用允许在其中使用 select 以及 DML 语句	函数中只允许使用 select 语句
异常可以通过过程中的 try-catch 块处理	函数中不能使用 try-catch 块
我们可以在过程中进行事务管理	我们不能去用于函数中的事务管理
过程不能在 select 语句中使用	函数可以嵌入在 select 语句中
过程可以影响数据库的状态，意味着可以对数据库执行 CRUD 操作	函数不能影响数据库的状态，意味着不能执行 CRUD 操作对数据库的操作
过程可以使用临时表	函数不能使用临时表
过程可以改变服务器环境参数	函数不能改变环境参数
当我们想要将一组可能复杂的 SQL 语句分组时，可以使用	过程函数可以使用 when我们想要计算并返回一个值以供其他 SQL 语句使用

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

回复收藏 0 原文

一袭水袖舞倾城 2024-08-05 17:43:24

当您想要计算并返回一个值以供其他 SQL 语句使用时，请编写用户定义的函数；当您想要将一组可能复杂的 SQL 语句分组时，请编写存储过程。毕竟，这是两个截然不同的用例！

回复收藏 0 原文

风月客 2024-08-05 17:43:24

基本区别

函数必须返回一个值，但在存储过程中它是可选的（过程可以返回零个或n个值）。

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

函数需要一个输入参数，这是强制性的，但存储过程可能需要 o 到 n 个输入参数。

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

高级差异

过程允许在其中使用 SELECT 以及 DML(INSERT/UPDATE/DELETE) 语句，而函数仅允许在其中使用 SELECT 语句。

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

存储过程不能在 SQL 语句中的 WHERE/HAVING/SELECT 部分中的任何位置使用，而函数可以。

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

内联函数可以被视为带有参数的视图，并且可以在 JOIN 和其他 Rowset 操作中使用。

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

我们可以在过程中进行事务管理，但不能在函数中进行。

来源

回复收藏 0 原文

岁月流歌 2024-08-05 17:43:24

用户定义函数是 sql server 程序员可用的重要工具。您可以在 SQL 语句中内联使用它，如下所示，

SELECT a, lookupValue(b), c FROM customers

其中 lookupValue 将是 UDF。使用存储过程时无法实现这种功能。同时，您无法在 UDF 内执行某些操作。这里要记住的基本事情是 UDF：

无法创建永久更改
无法更改数据

存储过程可以执行这些操作。

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

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

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:

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.

回复收藏 0 原文

鸩远一方 2024-08-05 17:43:24

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

函数 用作方法。你向它传递一些东西，它就会返回一个结果。应该小而快——即时执行。通常用在 SELECT 语句中。

回复收藏 0 原文

想念有你 2024-08-05 17:43:24

SQL Server 函数（如游标）旨在用作您的最后武器！它们确实存在性能问题，因此应尽可能避免使用表值函数。谈论性能是指在中级硬件的服务器上托管超过 1,000,000 条记录的表；否则您无需担心这些功能对性能造成的影响。

切勿使用函数将结果集返回到外部代码（如 ADO.Net）
尽可能使用视图/存储过程组合。有时，您可以使用 DTA（数据库调优顾问）提供的建议（如索引视图和统计信息）从未来的增长性能问题中恢复过来！

有关进一步参考，请参阅：http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-definition-function.html

回复收藏 0 原文

笨死的猪 2024-08-05 17:43:24

存储过程：

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

功能：

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

只能用于选择记录。然而，它可以被称为
在标准 SQL 中非常容易，例如：

SELECT dbo.functionname('参数1')

或

从 sysObjects 中选择名称、dbo.Functionname('Parameter1')

对于简单的可重用选择操作，函数可以简化代码。
只是要小心在函数中使用 JOIN 子句。如果你的
函数有一个 JOIN 子句，您可以从另一个选择中调用它
返回多个结果的语句，该函数调用将 JOIN
结果集中返回的每行的这些表在一起。所以
尽管它们有助于简化某些逻辑，但它们也可以是
如果使用不当，就会成为性能瓶颈。
使用 OUT 参数返回值。
不支持交易。

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 EXEC or EXECUTE statement.
Returns table variables, but we can't use OUT parameter.
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:
```
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.
Returns the values using OUT parameter.
Does not support transactions.