SQL 存储过程和用户定义函数的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2039936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between stored procedures and user defined functions
提问by vasu
Can anyone explain what is the exact difference between stored procedures and user defined functions, and in which context each is useful?
任何人都可以解释存储过程和用户定义函数之间的确切区别是什么，以及它们在哪种上下文中有用？
回答by Singleton
This is what i always keep in mind :)
这是我一直牢记的:)
- Procedure can return zero or n values whereas function can return one value which is mandatory.
- Procedures can have input/output parameters for it whereas functions can have only input parameters.
- Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
- Functions can be called from procedure whereas procedures cannot be called from function.
- Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
- We can go for transaction management in procedure whereas we can't go in function.
- Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
- UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
- UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
- 过程可以返回零个或 n 个值，而函数可以返回一个强制值。
- 过程可以有输入/输出参数，而函数只能有输入参数。
- 过程允许选择以及 DML 语句，而函数只允许选择语句。
- 函数可以从过程中调用，而过程不能从函数中调用。
- 异常可以由过程中的 try-catch 块处理，而 try-catch 块不能在函数中使用。
- 我们可以在程序中进行事务管理，而我们不能在功能中进行。
- 过程不能在 select 语句中使用，而函数可以嵌入在 select 语句中。
- UDF 可以在 WHERE/HAVING/SELECT 部分的 SQL 语句中使用，而存储过程不能。
- 返回表的 UDF 可以被视为另一个行集。这可以在与其他表的 JOIN 中使用。
- 内联 UDF 可以看作是采用参数的视图，并且可以在 JOIN 和其他 Rowset 操作中使用。
Source http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function
来源http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function
回答by OMG Ponies
A function always returns a value, and can notperform DML statements (INSERT/UPDATE/DELETE).
一个函数总是返回一个值，并且不能执行 DML 语句（INSERT/UPDATE/DELETE）。
A stored procedure can not return a value - you need to use an OUT parameter - and can run DML statements.
存储过程不能返回值——您需要使用 OUT 参数——并且可以运行 DML 语句。
Advantage of Using a Function vs a Stored Procedure?
使用函数与存储过程的优势？
Aside from the comparison above, they are equal. But given the comparison, depending on what you need to do it's likely you will use a stored procedure more often than you will a function.
除了上面的比较之外，它们是相等的。但是考虑到比较，根据您需要做什么，您可能会比使用函数更频繁地使用存储过程。
回答by gkpstar
User defined function has few limitiations like DML statments canbe used etc pls check
用户定义的函数几乎没有限制，例如可以使用 DML 语句等请检查
回答by Vinayak Savale
Differences:
区别：
Procedures can accept input(default), output and inout type parameters for it. Functions can accept only input type parameters.
Procedures may or may not return a value or may return more than one value using the OUTPUT and/or INOUT parameters. A procedure may return upto 1024 values through OUTPUT and/or INOUT parameters. Function always returns only one value.
Stored procedure returns always integer value by default zero. Function return type could be scalar or table or table values.
Stored procs can create a table but can't return table. Functions can create, update and delete the table variable. It can return a table
Stored Procedures can affect the state of the database by using insert, delete, update and create operations. Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
Stored procedures are stored in database in the compiled form. Function are parsed and conpiled at runtime only.
Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause. Function are called from select/where/having clause. Even we can join two functions.
Normally stored procedure will be used for perform specific tasks. Functions will be used for computing value. Stored procedure allows getdate () or other non-deterministic functions can be allowed. Function won't allow the non-deterministic functions like getdate().
In Stored procedures we can use transaction statements. We can't use in functions.
The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records. The function won't allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.
Temporary tables (derived) can be created in stored procedures. It is not possible in case of functions.
When sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code. In case of functions, T-SQL will stop execution of next statements.
过程可以为其接受输入（默认）、输出和输入输出类型参数。函数只能接受输入类型参数。
过程可能会或可能不会返回一个值，或者可能会使用 OUTPUT 和/或 INOUT 参数返回多个值。一个过程可以通过 OUTPUT 和/或 INOUT 参数返回多达 1024 个值。函数总是只返回一个值。
默认情况下，存储过程始终返回整数值为零。函数返回类型可以是标量或表或表值。
存储过程可以创建表但不能返回表。函数可以创建、更新和删除表变量。它可以返回一个表
存储过程可以通过使用插入、删除、更新和创建操作来影响数据库的状态。函数不能影响数据库的状态，这意味着我们不能对数据库执行插入、删除、更新和创建操作。
存储过程以编译后的形式存储在数据库中。函数仅在运行时解析和编译。
可以使用 exec 关键字独立调用存储过程。不能在 select/where/have 子句中使用存储过程。函数是从 select/where/have 子句中调用的。甚至我们可以连接两个函数。
通常存储过程将用于执行特定任务。函数将用于计算值。存储过程允许 getdate() 或其他非确定性函数也可以允许。函数不允许像 getdate() 这样的非确定性函数。
在存储过程中，我们可以使用事务语句。我们不能在函数中使用。
存储过程可以执行所有 DML 操作，例如插入新记录、更新记录和删除现有记录。该函数不允许我们像在存储过程中那样在数据库表中执行 DML 操作。它允许我们只做选择操作。它不允许在现有表上执行 DML。但是我们仍然只能对用户定义函数内的表变量进行 DML 操作。
可以在存储过程中创建临时表（派生表）。在函数的情况下是不可能的。
当 sql 语句遇到错误时，T-SQL 将忽略 SPROC 中的错误并继续执行剩余代码中的下一条语句。对于函数，T-SQL 将停止执行下一个语句。
Refer this link :
请参阅此链接：
