SQL Server 中的函数与存储过程

我一直在学习函数和存储过程很长一段时间，但我不知道为什么以及何时应该使用函数或存储过程。他们看起来和我一样，也许是因为我对此有点新手。

有人可以告诉我为什么吗？

答案

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

如果函数返回标量值，则可以在 SQL 语句中内联使用该函数; 如果返回结果集，则可以将其连接在一起。

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

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

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

函数和存储过程用于单独的目的。虽然它不是最好的类比，但函数可以像任何编程语言中使用的任何其他函数一样被视为字体，但存储过程更像是单个程序或批处理脚本。

功能通常具有输出和可选输入。然后输出可以用作另一个函数（内置的 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，则需要问自己，在父存储过程或其他 SQL 构造中使用 JOIN 是否可以更好地执行 SELECT（例如 CASE ... WHEN ... ELSE ... 结束）。