我需要为在 SQL Server 2008 中将多个参数传递给一系列报告存储过程的问题提供解决方案。这些参数将是用户首选项,并且可能包含用户已选择多种类型的值(例如多个月(的列表。可能有多达 30 个不同的参数,其中 60% 将包含多个选择。
到目前为止,我有 3 个选择。
-
使用设置的接口将值作为普通参数传入 - 这似乎很快就会失败,因为我需要传递集合(几个月就是一个很好的例子(。
-
将参数作为单个 XML 片段传入。这种方法已经在这里使用,尽管我不确定它是否可以处理相同类型的多个元素(例如,在上面的几个月中(。该代码使用 DynaFilter 来解析 XML - 我从未听说过它,在互联网上找不到任何参考资料。编写代码的开发人员明天回来了,所以我届时将提供更多信息。
-
使用表值参数 - 我才刚刚开始研究这些参数,但它们看起来很有前途并且似乎提供了良好的性能。
我们使用的是带有 ASP.Net MVC 前端的 SQL Server 2008。如果需要,我们可以转到2012年。
我已经开始并将继续对处理这个问题的最佳方法进行更多研究,但会重视关于最佳前进方向的任何意见以及是否有任何其他选择。
提前谢谢。
下面介绍了如何使用表值参数执行此操作。 这些是随 SQL 2008 一起引入的,因此,如果您选择采用此路线,该版本将适合您的解决方案。 在此测试场景中,我正在创建一个主表和一个具有指向主表的外键的相关表。
首先,创建表数据类型:
CREATE TYPE primary_tbltype AS TABLE
(personkey int NOT NULL PRIMARY KEY,
firstname varchar(30),
lastname varchar(30))
CREATE TYPE related_tbltype AS TABLE
(fk_personkey int NOT NULL,
accountnum varchar(30),
accountbalance money)
创建存储过程:
CREATE PROCEDURE MySproc
@PrimaryTable primary_tbltype READONLY,
@RelatedTable related_tbltype READONLY
AS
BEGIN
DECLARE @CurrentKey INT
DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)
DECLARE @AccountTotal MONEY
DECLARE PersonCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT personkey, firstname, lastname FROM @PrimaryTable
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
WHILE @@FETCH_STATUS= 0 BEGIN
SELECT @AccountTotal = SUM(accountbalance) FROM @RelatedTable
WHERE fk_personkey = @CurrentKey
PRINT @FirstName + ' ' + @LastName + ' - account total: ' + CONVERT(VARCHAR(30), @AccountTotal)
FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
END
END;
这里有一些测试数据可以尝试一下:
DECLARE @primaryTVP primary_tbltype
DECLARE @relatedTVP related_tbltype
INSERT INTO @primaryTVP values (1, 'John', 'Cleese')
INSERT INTO @primaryTVP values (2, 'Eric', 'Idle')
INSERT INTO @primaryTVP values (3, 'Graham', 'Chapman')
INSERT INTO @relatedTVP values (1, '29310918', 28934.33)
INSERT INTO @relatedTVP values (2, '123123', 3418.11)
INSERT INTO @relatedTVP values (2, '33333', 666.66)
INSERT INTO @relatedTVP values (3, '554433', 22.22)
INSERT INTO @relatedTVP values (3, '239482', 151515.15)
EXEC MySproc @primaryTVP, @relatedTVP;
此处介绍了使用 TVP 的一些优点,它们还能够参与基于集的操作。