提问



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


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

最佳参考


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


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


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



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


其它参考1


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

其它参考2


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


功能通常具有输出和可选输入。然后,输出可以用作另一个函数(内置的SQL Server,如DATEDIFF,LEN等)的输入,或者作为SQL查询的谓词 - 例如SELECT a, b, dbo.MyFunction(c) FROM tableSELECT 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 ...结束)。

其它参考3


当您想要计算并返回一个值以便在其他SQL语句中使用时,编写用户定义的函数;在需要时编写存储过程,而不是将可能复杂的SQL语句集合分组。毕竟,这是两个完全不同的用例!

其它参考4


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



  • 存储过程不能在Select语句中使用。

  • 存储过程支持延迟名称解析。

  • 存储过程通常用于执行业务逻辑。

  • 存储过程可以返回任何数据类型。

  • 存储过程可以接受比用户定义的函数更多的输入参数。存储过程最多可包含21,000个输入参数。

  • 存储过程可以执行动态SQL。

  • 存储过程支持错误处理。

  • 可以在存储过程中使用非确定性函数。







  • 用户定义的函数可用于Select语句。

  • 用户定义的函数不支持延迟名称解析。

  • 用户定义的函数通常用于计算。

  • 用户定义的函数应返回一个值。

  • 用户定义的函数无法返回图像。

  • 用户定义的函数接受的输入参数数量少于存储过程。 UDF最多可以有1,023个输入参数。

  • 临时表不能用于用户定义的函数。

  • 用户定义的函数无法执行动态SQL。

  • 用户定义的函数不支持错误处理。 RAISEERROR OR @@ERROR不允许在UDF中使用。

  • 不能在UDF中使用非确定性函数。例如,GETDATE()不能在UDF中使用。


其它参考5


基本差异


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


函数只能有输入参数,而程序可以有输入/输出参数。


函数需要一个输入参数,它是必需的,但存储过程可能需要o到n个输入参数。


可以从Procedure调用函数,但不能从Function调用过程。


提前差异


Procedure允许SELECT和DML(INSERT/UPDATE/DELETE)语句,而Function只允许SELECT语句。


程序不能在SELECT语句中使用,而Function可以嵌入SELECT语句中。


存储过程不能在WHERE/HAVING/SELECT部分​​的任何地方的SQL语句中使用,而Function可以。


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


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


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


我们可以在程序中进行交易管理,但我们不能进入功能。


[23]

其它参考6


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


SELECT a, lookupValue(b), c FROM customers 


其中lookupValue将是UDF。使用存储过程时无法使用此类功能。同时你不能在UDF中做某些事情。这里需要记住的基本事情是UDF:



  • 无法创建永久性更改

  • 无法更改数据



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


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

其它参考7


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


函数 用作方法。您传递了一些内容并返回结果。应该小而快 - 它是否在飞行中。

其它参考8


存储过程:



  • 就像是SQL Server中的微型程序。

  • 可以像select语句一样简单,也可以像long一样复杂
    用于添加,删除,更新和/或从多个数据中读取数据的脚本
    数据库中的表格。

  • (可以实现循环和游标,这两者都允许您使用
    较小的结果或对数据的逐行操作。)

  • 应使用EXECEXECUTE声明进行调用。

  • 返回表变量,但我们不能使用OUT参数。

  • 支持交易。



功能:



  • 不能用于更新,删除或添加记录到数据库。

  • 只需返回单个值或表值。

  • 只能用于选择记录。但是,它可以被称为
    很容易从标准SQL中,例如:


    SELECT dbo.functionname('Parameter1')
    


    要么


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

  • 对于简单的可重用选择操作,函数可以简化代码。
    请注意在函数中使用JOIN子句。如果你的
    function有一个JOIN子句,你可以从另一个select中调用它
    返回多个结果的语句,该函数调用将JOIN
    这些表一起用于结果集中返回的每个行。所以
    虽然它们可以帮助简化某些逻辑,但它们也可以是一个
    性能瓶颈,如果他们使用不当。

  • 使用OUT参数返回值。

  • 不支持交易。


其它参考9


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



  1. 存储过程无法返回表变量,而函数可以执行此操作。

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



干杯

其它参考10


像游标一样,SQL Server函数可以用作你的最后一个武器!它们确实存在性能问题,因此应尽可能避免使用表值函数。谈论性能是指在中型硬件上托管在服务器上的超过1,000,000条记录的表;否则你不必担心功能导致的性能损失。



  1. 永远不要使用函数将结果集返回到外部代码(如ADO.Net)

  2. 尽可能使用views/stored procs组合。你可以使用DTA(数据库调优顾问)给你的建议(比如索引视图和统计数据)来恢复未来的成长 - 性能问题 - 有时候!



有关进一步参考,请参阅:http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html [24]

其它参考11


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


然后,您可以将函数用于参数化的城市列表。 dbo.GetCitiesIn(NY)返回一个可用作连接的表。


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


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

其它参考12



  • 当函数不是存储过程时,必须返回一个值。

  • 选择仅在UDF中接受的语句,而不需要DML语句。

  • 存储过程接受任何语句和DML语句。

  • UDF只允许输入而不是输出。

  • 存储过程允许输入和输出。

  • Catch块不能在UDF中使用,但可以在存储过程中使用。

  • UDF中的函数不允许进行任何事务,但允许在存储过程中使用它们。

  • 只能在UDF中使用表变量,而不能在临时表中使用。

  • 存储过程允许表变量和临时表。

  • UDF不允许从函数调用存储过程,而存储过程允许调用函数。

  • UDF用于join子句,而存储过程不能用于join子句。

  • 存储过程将始终允许返回零。相反,UDF的值必须回到预定的点。


其它参考13


这里更倾向于使用存储过程的函数。如果你有一个存储过程需要另一个存储过程的结果,你必须使用insert-exec语句。这意味着你必须创建一个临时表并使用一个exec语句将存储过程的结果插入到临时表中。这很麻烦。这样做的一个问题是insert-execs不能嵌套。[25]


如果你坚持使用调用其他存储过程的存储过程,你可能会遇到这种情况。如果嵌套存储过程只返回一个数据集,它可以用表值函数替换,你将不会再遇到这个错误。


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

其它参考14


              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 not affect the state  | * Function can not affect the state of
   of database means it can not perform|   database means it can not perform 
   CRUD operation on database.         |   CRUD operation on database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
   temporary tables                    |   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.

其它参考15



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

  • 存储过程同时接受输入和输出参数,但函数仅接受输入参数。

  • 函数无法返回text,ntext,image&类型的值。程序可以的时间戳。

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



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


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

其它参考16


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


功能:在SQL Server数据库中,这些函数用于执行某些操作,操作会立即返回结果。
功能有两种:



  1. 系统定义

  2. 用户定义



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



  1. 系统存储过程

  2. 用户定义程序