如何使用SQL CLR表值函数进行扩展

核心提示：若要调试 GetProximity 函数，请在 C# 代码中为该函数设置一个断点，如何使用SQL CLR表值函数进行扩展(13)，并再次运行该脚本，在指定的点，图 3. 布局模式下的报表图像图 4 显示绘制有 ATM 位置图的报表结论本白皮书说明了如何使用 SQL Server 中的表值函数来扩展 SQL Server

若要调试 GetProximity 函数，请在 C# 代码中为该函数设置一个断点，并再次运行该脚本。在指定的点，执行将中止，您可以像对任何其他托管过程一样对它进行调试。

使用 MapPoint Web 服务创建报表：

随 SQL Server 2005 附带的 AdventureWorks 示例数据库描述了虚构的自行车和自行车配件制造商，该厂商向全美国的零售店出售产品。在本示例中，Adventure Works Cycles 决定停止接受信用卡或支票。从现在起，他们希望所有发票都只以现金支付。在此过渡期间，为了方便其客户，该厂商正在制作一份报表，在其中显示距离其客户的零售店位置最近的五台 ATM 的地址和地图。这不是一个现实场景，但可以起到说明如何使用表值函数将传统数据源（SQL 数据库）与非传统数据源（MapPoint Web 服务）联接在一起的目的。

创建我们的报表的第一步是在 Visual Studio 中创建一个新的 Report Server 项目，并指定数据源。我的报表的数据源是 SQL Server 2005 AdventureWorks 示例数据库，其中有我以前创建的 MapPoint 表值函数。有一个用于该报表的数据集。它包含零售店名、零售店所在的城市、州、ATM 名、ATM 地址和 ATM 路线图等字段。

对于每个零售店，我们都希望调用 GetProximity 并找出五台最近的 ATM。在 SQL Server 2005 中，有一个新的 APPLY 子句类型可处理此问题。这与联接稍有不同，因为我们希望联接函数参数，而不是函数结果。这意味着表值函数是为 APPLY 左侧返回的每一行调用的。然后，就可以将函数结果的并集联接到该查询的其余部分。以下是该报表数据集的 Transact-SQL 查询：

SELECT TOP(40) Sales.Store.Name, Person.Address.City,
　 Person.StateProvince.StateProvinceCode, GetProximity_1.HitName,
　 GetProximity_1.HitAddress, GetProximity_1.MapImage
　 FROM Sales.CustomerAddress
　　　INNER JOIN Person.Address
　　　　 ON Sales.CustomerAddress.AddressID = Person.Address.AddressID
　　　　 AND Sales.CustomerAddress.AddressID = Person.Address.AddressID
　　　INNER JOIN Sales.Store
　　　INNER JOIN Sales.StoreContact ON Sales.Store.CustomerID =
Sales.StoreContact.CustomerID
　　　　 ON Sales.CustomerAddress.CustomerID =
Sales.StoreContact.CustomerID
　　　INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID
= Person.StateProvince.StateProvinceID
　　　　 AND Person.Address.StateProvinceID =
Person.StateProvince.StateProvinceID
CROSS APPLY dbo.GetProximity(Person.Address.City,
　 Person.StateProvince.StateProvinceCode, 5, 'SIC3578') AS
GetProximity_1

请注意，其中使用了 CROSS APPLY 来将 GetProximity 函数参数与 Person.Address.City 和 Person.StateProvince.StateProvinceCode 表示的其他查询数据进行链接。

注意 若要在数据集查询中使用 APPLY 子句，必须使用通用查询设计器。基于 GUI 的查询设计器不能以图形方式显示它，并会引发异常。

我的报表设计使用两个嵌套的列表。内部列表包含用于 ATM 名称和地址的文本框，以及地图的图像。在我的报表中，此图像设置为 AutoSize，因此它会根据 Web 服务中的图像的大小而调整。外部列表包含用于零售店的名称和位置的文本框。外部列表按零售店名称进行分组。我的报表在布局模式下的图像如图 3 所示。

图 3. 布局模式下的报表图像

图 4 显示绘制有 ATM 位置图的报表

结论

本白皮书说明了如何使用 SQL Server 中的表值函数来扩展 SQL Server Reporting Services 的数据访问功能。表值函数让编程人员和报表设计员有一定的灵活性，使他们能够实现数据不直接存储在数据库表中的报表制作场景。