作者:Teo Lachev
发表日期：August 21, 2009
译者：Daniel_Zhen
在现实世界中，很少会有从一个侧面了解数据的需求。如果你想从多个复杂的角度了解数据，你将需要使用多值变量（multivalued parameters）。
本文将示例一种在SQL Server存储过程中集成多值变量的方法，它将利用到表值变量（TVPs）,即SQL Server 2008的一种新特性。本示例需要安装AdventureWorksDW2008数据库，该库可以从微软网站上下载。
解析变量值
你可以在Reporting Services中为过滤数据、连接相应报表、以及不同的报表展示功能定义报表变量。从SQL Server 2005开始，SQL Server Reporting Services (SSRS)开始支持多值变量，用户可以为报表选择多个输入变量或值。举例来说，用户可以选择美国和英国来生成展示来自这两个国家的数据。当一个报表变量被配置为多值变量，Reporting Services自动产生一个包含输入值的以逗号分割的字符串，该字符串被传输给报表进行查询。在报表查询中应用多值变量，你必须在SELECT语句的WHERE条目下拓展一个IN操作符。当你使用一个基于SQL语句的报表数据集时，这么做是有效的。
但是你是否需要把多值变量传递到存储过程中呢？在SQL Server 2005版的Reporting Services中，你需要在存储过程中解析以逗号分割的字符串来提取变量值。逗号分割的变量字符串很难进行执行和调试，但SQL Server 2008新特性之一的表值变量(TVP)正好解决了该问题。TVP提供了一个内部机制传把多行数据作为一个单变量传给存储过程。该功能免去了文本解析代码，简化了存储过程中多值变量的传输。
2008年地区销售情况报表如图1所示，你可以使用TVP特性把多值变量传给存储过程。该报表用到了Reporting Services 2008的图表功能，该功能在这一版中得到了加强。示例展示了的图表特性之一的“空点绘图”——在2003年7月以前，太平洋区域的红叉表示没有该区域数据。
要应用TVP，你先要创建一个用户定义表类型。SQL 脚本如列表1所示，包含名为TerritoryType的用户自定义表数据类型和名为uspGetSalesByTerritory2008的存储过程。
列表 1
CREATE TYPE [dbo].[TerritoryType] AS TABLE (
[TerritoryKey] [int] NOT NULL,
[TerritoryName] [nvarchar](50) NOT NULL
)
GO
CREATE PROCEDURE [dbo].[uspGetSalesByTerritory2008] (
@CalendarYear int,
@Territory TerritoryType READONLY
)
AS
BEGIN
SET NOCOUNT ON;
SELECT ST.SalesTerritoryGroup AS TerritoryGroup, D.FullDateAlternateKey AS [Date],
SUM(FRS.SalesAmount) AS ResellerSalesAmount
FROM DimDate AS D INNER JOIN FactResellerSales AS FRS ON D.DateKey = FRS.OrderDateKey
INNER JOIN DimSalesTerritory AS ST ON FRS.SalesTerritoryKey = ST.SalesTerritoryKey
INNER JOIN @Territory AS T ON ST.SalesTerritoryKey = T.TerritoryKey
WHERE (D.CalendarYear = @CalendarYear)
GROUP BY ST.SalesTerritoryGroup, D.FullDateAlternateKey
END
CREATE TYPE语句创建了名为TerritoryType的TVP。TerritoryType表定义了两列。TerritoryKey列将存储区域标识，TerritoryName列将存储区域名称。严格来说，TerritoryName列并不需要，但是我还是为了参考而引入了它，这同时也是实现文档的最佳实践。
CREATE PROCEDURE语句创建了名为uspGetSalesByTerritory2008的存储过程，并把@CalendarYear 和 @TerritoryType作为变量。你将注意到，你并不需要任何代码来解析变量值，因为TVP已经让变量就绪。为了约束数据，你可以简单的连接SalesTerritory表和TerritoryType TVP，正如你连接一个通用的表。
定义报表查询
当存储过程就绪，你可以设计报表查询并使用它。Reporting Services并不直接支持TVP，但是你可以使用基于表达式的查询产生语句用来装载存储过程变量并执行存储过程。列表2展示了如果用户选择2004年、北美和太平洋地区。使用这一语法，在列表3中的报表属性编码框中加入GetQuery函数。
列表 2
DECLARE @CalendarYear int = 2004
DECLARE @Territory TerritoryType
insert into @Territory values (1, 'North America')
insert into @Territory values (9, 'Pacific')
EXECUTE [dbo].[uspGetSalesByTerritory2008] @CalendarYear, @Territory
列表 3
Public Function GetQuery(ByVal calendarYear As Integer, ByVal values As Object(),
ByVal labels As String()) As String
Dim sb As New System.Text.StringBuilder()
sb.AppendLine([String].Format("DECLARE @CalendarYear int = {0}", calendarYear))
sb.AppendLine("DECLARE @Territory TerritoryType")
For i As Integer = 0 To values.Length - 1
sb.AppendLine([String].Format("insert into @Territory values ({0}, '{1}')",
values(i), labels(i)))
Next
sb.AppendLine("EXECUTE [dbo].[uspGetSalesByTerritory2008] @CalendarYear,@Territory")
Return sb.ToString()
End Function
Next, I changed the report data set to use the following expression-based query:
=Code.GetQuery
(Parameters!CalendarYear.Value, Parameters!Territory.Value, Parameters!Territory.Label)
在运行时，该表达式调用了GetQuery函数，传输了变量值。区域变量的所选值将作为对象数组传输，标签将作为字符串数组传输。GetQuery使用StringBuilder对象来创建所需的T-SQL语句。首先，它定义了CalendarYear变量。接下来，它循环处理Territory变量输入值，产生T-SQL代码，插入所选的区域至TerritoryType TVP。最后，它附加的语句用来执行存储过程和传输变量。因为GetQuery方法只能处理字符串，默认的.NET代码访问安全执行许可对于成功执行GetQuery方法是足够的。
原文链接：
http://www.sqlmag.com/Articles/ArticleID/102389/pg/2/2.html
译者介绍：
甄浩，北京迈思奇BI开发工程师，资深BI咨询顾问，软件工程专业硕士。
主攻数据仓库、OLAP建模、数据挖掘、企业绩效管理等技术领域，具有6年的BI项目实施和咨询经验。
转载请注明出处。 |
