SQL服务器中的函数与存储过程

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

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

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

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

当你想要计算并返回一个值以用于其他SQL语句时，编写一个用户定义的函数；当你想要对一组可能复杂的SQL语句进行分组时，编写一个存储过程。

决定何时使用以下几点可能会有所帮助-

1. 存储过程不能返回表变量，而as函数可以这样做。

2. 您可以使用存储过程来更改服务器环境参数，而使用函数则不能。

欢呼

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

SELECT a, lookupValue(b), c FROM customers

其中lookupValue将是一个UDF。当使用存储过程时，这种功能是不可能的。同时，你不能在UDF中做某些事情。这里要记住的基本事情是UDF的：

• 不能创建永久更改
• 不能更改数据

存储过程可以做这些事情。

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

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

函数通常有一个输出和可选的输入。输出可以用作另一个函数（SQL服务器内置的，如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 INTEGERASBEGINDECLARE @retval INTEGER
SELECT localValueFROM dbo.localToNationalMapTableWHERE nationalValue = @someValue
RETURN @retvalEND

这里发生的事情是为表MyTable中的每一行调用函数MyFunction。如果MyTable有1000行，那么这是对数据库的另外1000个即席查询。类似地，如果在列规范中指定时调用该函数，那么SELECT返回的每一行都会调用该函数。

所以你确实需要小心编写函数。如果你从函数中的表中进行SELECT，你需要问自己是否可以更好地使用父存储过程中的JOIN或其他一些SQL构造（例如CASE… WHEN… ELSE… END）来执行它。

SQL服务器函数，就像光标一样，是用来作为最后的武器的！它们确实存在性能问题，因此应该尽可能避免使用表值函数。谈论性能是指托管在中产阶级硬件上的服务器上的记录超过1,000,000条的表；否则你不必担心函数导致的性能下降。

1. 永远不要使用函数将结果集返回给外部代码（如ADO. Net）
2. 尽可能使用视图/存储进程组合。您可以使用DTA（数据库调优顾问）会给您的建议（如索引视图和统计信息）从未来的增长性能问题中恢复-有时！

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

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

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

• 函数不能返回text、ntext、图像和时间戳类型的值，而过程可以。

• 函数可以用作创建表中用户定义的数据类型，但过程不能。

***例如：-创建table <tablename>(name varchar(10),salary getsal(name))

这里gettal是一个用户定义的函数，它返回一个工资类型，当表被创建时，没有为工资类型分配存储空间，并且getsal函数也不执行，但是当我们从这个表中获取一些值时，gettal函数get的执行和返回类型作为结果集返回。

SP和UDF的区别如下：

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

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

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

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

然后，您可以使用一个函数来参数化城市列表。dbo. GetCitiesIn（"NY"）返回一个可用作连接的表。

这是一种组织代码的方式。只有通过反复试验和经验才能知道什么时候可以重用，什么时候浪费时间。

此外，函数在SQL服务器中是一个好主意。它们更快，并且可以非常强大。内联和直接选择。小心不要过度使用。

• 函数必须返回一个值，而它不适用于存储过程。
• 选择仅在UDF中接受的语句，而不需要DML语句。
• 存储过程接受任何语句以及DML语句。
• UDF只允许输入，不允许输出。
• 存储过程允许输入和输出。
• 捕获块不能在UDF中使用，但可以在存储过程中使用。
• 在UDF函数中不允许事务，但在存储过程中允许事务。
• 只有表变量可以在UDF中使用，而不是临时表。
• 存储过程允许表变量和临时表。
• UDF不允许从函数调用存储过程，而存储过程允许调用函数。
• UDF在连接子句中使用，而存储过程不能在连接子句中使用。
• 存储过程将始终允许返回零。相反，UDF具有必须返回到预定点的值。

基本差异

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

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

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

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

提前差额

过程中允许SELECT以及DML（INSERT/UPDATE/DELETE）语句，而函数中只允许SELECT语句。

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

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

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

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

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

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

来源

存储过程：

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

功能：

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

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

  SELECT dbo.functionname('Parameter1')

  或

  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 yourfunction has a JOIN clause and you call it from another selectstatement that returns multiple results, that function call will JOINthose tables together for each line returned in the result set. Sothough they can be helpful in simplifying some logic, they can also be aperformance bottleneck if they're not used properly.

• Returns the values using OUT parameter.
• Does not support transactions.

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

函数它们被用作方法。你传递一些东西，它会返回一个结果。应该小而快-动态执行。通常用于SELECT语句。

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

功能：在SQLServer数据库中，函数用于执行某些操作，并且操作立即返回结果。函数有两种类型：

1. 系统定义

2. 用户自定义

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

1. 系统存储过程
2. 用户定义过程

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

如果你被困在调用其他存储过程的存储过程中，你可能会遇到这种情况。如果嵌套存储过程只是返回一个数据集，它可以用表值函数替换，你将不再收到此错误。

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

我意识到这是一个非常古老的问题，但我没有看到任何答案中提到的一个关键方面：内联到查询计划中。

功能可以…

1. 标量：

   CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END

2. 多语句表值：

   CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END

3. 内联表值：

   CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

第三种（内联表值）基本上被查询优化器视为（参数化）视图，这意味着从查询中引用函数类似于复制粘贴函数的SQL体（没有实际复制粘贴），从而带来以下好处：

• 查询规划器可以像优化任何其他子查询一样优化内联函数的执行（例如，消除未使用的列、向下推送谓词、选择不同的JOIN策略等）。
• 组合多个内联函数不需要在将第一个函数的结果提供给下一个函数之前将其具体化。

上述措施可能会带来显著的性能节省，尤其是在组合多个级别的功能时。

注意：看起来SQLServer 2019也将引入某种形式的标量函数内联。

通常使用存储过程更适合执行。例如，在以前版本的SQLServer中，如果将函数置于JOIN条件下，则基数估计值为1（SQL2012之前）和100（SQL2012之后和SQL2017之前），并且引擎可能会生成错误的执行计划。

此外，如果您将其放在WHERE子句中，SQL引擎可能会生成错误的执行计划。

2017年SQL，微软推出了交错执行功能，以产生更准确的估计，但存储过程仍然是最好的解决方案。

更多细节，请查看Joe Sack的以下文章https://techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417