如何在SQL Server中构建并利用UDF表格

在SQL Server 2000和2005中，UDF（用户定义函数）表格是我最喜欢的特性之一。如果你还没有用过它们，可以试一下，它会带给你很多惊喜的。

UDF表格和返回一个记录集的存储过程类似。你可以通过如下简单的示例代码，得到一个两列无行或数行的记录集。

CREATE PROCEDURE simple_ap
( @cityID integer )
AS
      SELECT CustomerID, CompanyName
      FROM Customers
      WHERE Customers.CityID = @cityID

要重复利用这样一个存储过程的结果没有简单的方法。例如，你不能将记录集应用于其他的程序，或者将其添加到一个表格、视图或另一个记录集中。如果可以做到这一点的话，那么你重复利用代码的机会就会大大增加，并且，你还可以据此自己设计代码。

进入UDF表格，这种构造使你能够像处理一个真正的表格或视图那样对待结果集——你可以将它加入到其他的对象中，对它添加一个WHERE字句，甚至更多。

以下的代码中包括了在SQL Server 2000中创建UDF表格的句法：

CREATE FUNCTION [owner].[function_name]
(
      @parm1 <datatpe> = <default>
)
RETURNS TABLE
AS
      RETURN
      (
      SELECT <column1, column2, ...>
      FROM <table, view, etc.>
      WHERE <some condition applies>      -- optional clauses
)

要将以上的存储过程转换成为一个UDF表格，只需用适当的块替换UDF句法中的位置标记就行了，就像下面这样：

CREATE FUNCTION [dbo].[Customers_By_City_Select]    -- denotes table UDF
(
      @city nvarchar(15)
)
RETURNS TABLE
AS
      RETURN
      (
      SELECT CustomerID, CompanyName
      FROM dbo.Customers
      WHERE Customers.City = @city
      )

结果集是相同的，但是它却有一些很好的新优点。首先，利用过程的话，调用方法如下：

EXEC simple_ap 12345

而对UDF的调用是这样的：

SELECT * FROM Customers_By_City_Select ('Berlin')

我们注意到，如果利用UDF的话，我们可以向调用添加DISTINCT命令，用它来减少该示例代码中的结果集：

ALFKIAlfredsFutterkiste
ANATRAna Trujillo Emparedados y helados
ANTONAntonio Moreno Taquería
AROUTAround the Horn

这不仅证明一个表格函数就是一个真实的表格，而且也表明允许这种灵活性的过程将肯定会更加复杂。