在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

其他回答

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

SELECT a, lookupValue(b), c FROM customers 

其中lookupValue将是UDF。这种功能在使用存储过程时是不可能实现的。同时,您不能在UDF中做某些事情。这里要记住的基本事情是UDF的:

不能产生永久的变化 无法更改数据

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

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

下面是一个总结差异的表格:

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

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

如果您使用调用其他存储过程的存储过程,可能会遇到这种情况。如果嵌套存储过程只是返回一个数据集,则可以将其替换为表值函数,这样就不会再出现此错误。

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

SQL Server函数,比如游标,是你最后的武器!它们确实存在性能问题,因此应该尽可能避免使用表值函数。谈论性能就是谈论一个有超过1,000,000条记录的表托管在一个中产阶级硬件的服务器上;否则,您不需要担心函数对性能的影响。

永远不要使用函数将结果集返回给外部代码(如ADO.Net) 尽可能使用视图/存储procs组合。您可以使用DTA(数据库调优顾问)给您的建议(比如索引视图和统计数据)来解决未来的增长性能问题——有时!

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