SQL 函数与存储过程

Functions vs Stored Procedures

提问by Auron

Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use?

假设我必须实现一段必须返回表作为结果的 T-SQL 代码。我可以实现一个表值函数或一个返回一组行的存储过程。我应该使用什么？

In short, what I want to know is:

总之，我想知道的是：

Which are the main differences between functions and stored procedures?What considerations do I have to take into account for using one or the other?

函数和存储过程之间的主要区别是什么？使用其中一种时我必须考虑哪些因素？

采纳答案by Damien_The_Unbeliever

If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.

如果您可能希望将这段代码的结果与其他表组合，那么显然表值函数将允许您在单个 SELECT 语句中组合结果。

Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.

通常，有一个层次结构（View < TV Function < Stored Proc）。您可以在每一个中做更多的事情，但是随着功能的增加，组合输出和优化器真正参与的能力会降低。

So use whichever one minimally allows you to express your desired result.

因此，使用最低限度允许您表达所需结果的任何一种。

回答by Eric Z Beard

Functions must be deterministic, and cannot be used to make changes to the database, whereas stored procedures allow you to do inserts and updates, etc.

函数必须是确定性的，不能用于更改数据库，而存储过程允许您进行插入和更新等。

You should limit your use of functions, since they pose a huge scalability problem for big, complex queries. They become sort of a "black box" for the query optimizer, and you'll see enormous differences in performance between using functions and simply inserting the code into a query.

您应该限制对函数的使用，因为它们对大型复杂查询造成了巨大的可扩展性问题。它们成为查询优化器的某种“黑匣子”，您会发现使用函数和简单地将代码插入查询之间的性能差异很大。

But they are definitely useful for table-valued returns in very specific cases.

但在非常特殊的情况下，它们对于表值返回绝对有用。

If you need to parse a comma-delimited list, to simulate passing an array to a procedure, a function can turn the list into a table for you. This is common practice with Sql Server 2005, since we can't pass in tables to stored procedures yet (we can with 2008).

如果您需要解析逗号分隔的列表，以模拟将数组传递给过程，函数可以为您将列表转换为表格。这是 Sql Server 2005 的常见做法，因为我们还不能将表传递给存储过程（我们可以在 2008 中）。

回答by Christoffer Lette

From the docs:

从文档：

If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:

• The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters.

• The stored procedure does not perform update operations, except to table variables.

• There is no need for dynamic EXECUTE statements.

• The stored procedure returns one result set.

• The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement.

如果存储过程满足以下条件，则很适合将其重写为表值函数：

• 该逻辑可在单个 SELECT 语句中表达，但它是一个存储过程，而不是视图，只是因为需要参数。

• 存储过程不执行更新操作，除了表变量。

• 不需要动态 EXECUTE 语句。

• 存储过程返回一个结果集。

• 存储过程的主要目的是构建要加载到临时表中的中间结果，然后在 SELECT 语句中进行查询。

回答by Neeraj Kumar Yadav

I am going to write few interesting differences between stored procedures and functions.

我将在存储过程和函数之间写一些有趣的区别。

• We can use functions in select queries but we cannot use stored procedures in select queries.

• We cannot use non deterministic functions in Functions but we can use non deterministic functions in stored procedures. Now question comes up, what is non deterministic function.. Ans is:-

  A non deterministic function is that function which returns different outputs for same input values at different time, like getdate(). It always returns different value whenever it is run.

  Exception:-

  Earlier versions of sql server prior to sql 2000 do not allow to use getdate() function in user defined functions, but version 2005 and onward allows us to use getdate() function within a user defined function.

  Newid() is another example of non deterministic function but cannot be used in user defined functions but we can use it in stored procedure.

• We can use DML(insert, update, delete) statements within a stored procedure but we cannot use DML statements in functions on physical tables or permanent tables. If we want to do DML operation in functions we can do it over table variables not on permanent tables.

• We cannot use error handling within function but we can do error handling in stored procedures.

• 我们可以在选择查询中使用函数，但不能在选择查询中使用存储过程。

• 我们不能在函数中使用非确定性函数，但我们可以在存储过程中使用非确定性函数。现在问题来了，什么是非确定性函数.. 答案是：-

  非确定性函数是在不同时间为相同输入值返回不同输出的函数，例如 getdate()。每当它运行时，它总是返回不同的值。

  例外：-

  sql 2000 之前的早期版本的 sql server 不允许在用户定义的函数中使用 getdate() 函数，但 2005 及以后的版本允许我们在用户定义的函数中使用 getdate() 函数。

  Newid() 是非确定性函数的另一个例子，但不能在用户定义的函数中使用，但我们可以在存储过程中使用它。

• 我们可以在存储过程中使用 DML（插入、更新、删除）语句，但不能在物理表或永久表的函数中使用 DML 语句。如果我们想在函数中进行 DML 操作，我们可以对表变量而不是永久表进行操作。

• 我们不能在函数内使用错误处理，但我们可以在存储过程中进行错误处理。

回答by nathan1138

1. Procedure can return zero or n values whereas function can return one value which is mandatory.

2. Procedures can have input/output parameters for it whereas functions can have only input parameters.

3. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

4. Functions can be called from procedure whereas procedures cannot be called from function.

5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6. We can go for transaction management in procedure whereas we can't go in function.

7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

8. UDF (User Defined function) can be used in the SQL statements anywhere in the WHERE/HAVING/SELECTsection whereas stored procedures cannot be.

9. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

10. Inline UDFs can be though of as views that take parameters and can be used in JOINs and other rowset operations.

1. 过程可以返回零个或 n 个值，而函数可以返回一个强制值。

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

3. 过程允许选择以及 DML 语句，而函数只允许选择语句。

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

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

6. 我们可以在程序中进行事务管理，而我们不能在功能中进行。

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

8. UDF（用户定义函数）可以在任何地方SQL语句中使用WHERE/ HAVING/SELECT部分而存储过程是不可能的。

9. 返回表的 UDF 可以被视为另一个行集。这可以在JOINs 中与其他表一起使用。

10. 内联 UDF 可以看作是采用参数的视图，并且可以在JOINs 和其他行集操作中使用。

回答by Ilya Kochetov

If you have a function you could use it as a part of your SQL statement, for example

如果您有一个函数，您可以将其用作 SQL 语句的一部分，例如

SELECT function_name(field1) FROM table

It does not work this way for the stored procedures.

它不适用于存储过程。

回答by Paul Grimshaw

I ran some tests with a long running bit of logic, with the same bit of code (a long SELECT statement) running in both a Table Valued Function and a Stored Procedure, and a straight EXEC/SELECT, and each performed identically.

我用长时间运行的逻辑运行了一些测试，在表值函数和存储过程中运行了相同的代码（一个长的 SELECT 语句），以及一个直接的 EXEC/SELECT，并且每个都执行相同。

In my opinion always use a Table Valued Function rather than a stored procedure to return a result set, as it makes logic much easier and readable in queries that subsequently join to them, and enables you to reuse the same logic. To avoid too much of a performance hit, I often use "optional" parameters (i.e. you can pass NULL to them) to enable the function to return the result set to be quicker, e.g.:

在我看来，总是使用表值函数而不是存储过程来返回结果集，因为它使随后加入它们的查询中的逻辑更容易和可读，并使您能够重用相同的逻辑。为了避免过多的性能影响，我经常使用“可选”参数（即您可以将 NULL 传递给它们）来使函数能够更快地返回结果集，例如：

CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN 
    SELECT DISTINCT SiteID, PersonID
    FROM dbo.SiteViewPermissions
    WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
    AND (@optSiteID IS NULL OR @optSiteID = SiteID)
    AND @RegionID = RegionID

This way you can use this function for many different situations, and don't take a huge performance hit. I believe this is more efficient than filtering afterwards:

通过这种方式，您可以在许多不同的情况下使用此功能，并且不会对性能造成巨大影响。我相信这比之后过滤更有效：

SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1

I have used this technique in several functions, sometimes with a long list of "optional" parameters of this type.

我在几个函数中使用了这种技术，有时还有一长串这种类型的“可选”参数。

回答by 6eorge Jetson

As mentioned above, functions are more readable/composable/self documenting, but are less performant in general, and can be seriously less performant if you get carried away with them in joins such as

如上所述，函数更具可读性/可组合性/自文档化，但总体上性能较低，如果您在诸如此类的连接中对它们束手无策，则性能可能会严重下降

SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
    ON (tvf1.JoinId = tvf2.JoinId)

Often, you just have to accept the redundancy of code that a tvf could eliminate (at a unacceptable performance cost.)

通常，您只需要接受 tvf 可以消除的代码冗余（以不可接受的性能成本）。

One other point I haven't yet seen mentioned is that you can't use database state-changing temp tables inside of a multi-statement tvf.The most functionally equivalent mechanism to a temp table is the non-state changing, in memory table variable, and for large datasets, a temp table will likely be more performant than a table variable. (Other alternatives include dynamic tables & common table valued expressions, but at some level of complexity, these cease to be a good option IMO.)

我还没有提到的另一点是您不能在多语句 tvf 中使用数据库状态更改临时表。与临时表在功能上最等效的机制是非状态更改，内存表变量，对于大型数据集，临时表可能比表变量的性能更高。（其他替代方案包括动态表和公用表值表达式，但在某种程度的复杂性上，这些不再是 IMO 的好选择。）

回答by wcm

I personally use table valued functions when all I am returning is a single table with no affects. Basically I treat them like parameterized views.

当我返回的只是一个没有影响的表时，我个人使用表值函数。基本上我把它们当作参数化视图。

If I need multiple recordsets returned or if there will be values updated in tables, I use a stored procedure.

如果我需要返回多个记录集或者表中的值会更新，我会使用存储过程。

My 2 cents

我的 2 美分

回答by edosoft

It depends :) If you want to use the table-valued result in another procedure, you're better of using a TableValued Function. If the results is for a client, the stored proc is usualy the better way to go.

这取决于:) 如果您想在另一个过程中使用表值结果，最好使用 TableValued 函数。如果结果是针对客户端的，则存储过程通常是更好的方法。