功能与存储过程

假设我必须实现一段必须返回表的T-SQL代码.我可以实现一个表值函数或一个返回一组行的存储过程.我该怎么用？

简而言之,我想知道的是:

哪些是函数和存储过程之间的主要区别？使用其中一个时需要考虑哪些因素？

1> Damien_The_U..：

---

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

通常,有一个层次结构(View

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

2> Eric Z Beard..：

---

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

您应该限制对函数的使用,因为它们会给大型复杂查询带来巨大的可伸缩性问题.它们成为查询优化器的"黑盒子",你会发现使用函数和简单地将代码插入查询之间的性能差异很大.

但在特定情况下,它们对于表值返回肯定有用.

如果需要解析以逗号分隔的列表,以模拟将数组传递给过程,则函数可以将列表转换为表格.这是Sql Server 2005的常见做法,因为我们还不能将表传递给存储过程(我们可以使用2008).

---

错误,大多数SQL服务器功能都是非确定性的,例如MS-SQL服务器中的getdate.只有ODBC函数是规范函数(=更快+可索引)...但是你是非常正确的,出于性能原因,应该尽可能地限制查询中函数的使用.

3> Christoffer ..：

---

来自文档:

如果存储过程满足以下条件,则可以将其重写为表值函数:

逻辑可以在单个SELECT语句中表达,但只是因为需要参数而是存储过程而不是视图.

除表变量外,存储过程不执行更新操作.

不需要动态EXECUTE语句.

存储过程返回一个结果集.

存储过程的主要目的是构建要加载到临时表中的中间结果,然后在SELECT语句中查询该临时表.

4> Neeraj Kumar..：

---

我将在存储过程和函数之间写下一些有趣的差异.

我们可以在select查询中使用函数,但我们不能在select查询中使用存储过程.

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

非确定性函数是在不同时间为相同输入值返回不同输出的函数,如getdate().无论何时运行,它总是返回不同的值.

例外:-

在sql 2000之前的早期版本的sql server不允许在用户定义的函数中使用getdate()函数,但是版本2005及更高版本允许我们在用户定义的函数中使用getdate()函数.

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

我们可以在存储过程中使用DML(插入,更新,删除)语句,但是我们不能在物理表或永久表的函数中使用DML语句.如果我们想在函数中执行DML操作,我们可以在不在永久表上的表变量上执行它.

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

5> nathan1138..：

---

过程可以返回零或n值,而函数可以返回一个必需的值.

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

Procedure允许select和DML语句,而function只允许select语句.

可以从过程调用函数,而不能从函数调用过程.

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

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

程序不能在select语句中使用,而函数可以嵌入select语句中.

UDF(用户定义函数)可以在WHERE/ HAVING/ SELECT部分中的任何位置的SQL语句中使用,而存储过程则不能.

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

内联UDF可以作为带有参数的视图,可以在JOINs和其他行集操作中使用.

6> Ilya Kocheto..：

---

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

SELECT function_name(field1) FROM table

它对存储过程不起作用.

7> Paul Grimsha..：

---

我运行了一些具有长时间逻辑运算的测试,在表值函数和存储过程中运行相同的代码(一个长SELECT语句),并且直接执行EXEC/SELECT,每个执行相同.

在我看来,总是使用表值函数而不是存储过程来返回结果集,因为它使逻辑在随后加入它们的查询中更容易和可读,并使您能够重用相同的逻辑.为了避免过多的性能损失,我经常使用"可选"参数(即您可以向它们传递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

这样,您可以将此功能用于许多不同的情况,并且不会受到巨大的性能影响.我相信这比后续过滤更有效:

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

我在几个函数中使用过这种技术,有时会使用这种类型的"可选"参数列表.