DBA 架构师高级中级开发 SQL Server SQL

SQL 魔法第四部分 - 将数据列表传递给 SQL Server

Christian Graus

4.74/5 (18投票s)

2014 年 1 月 4 日

CPOL

8分钟阅读

33189

336

将任意值列表传递给 SQL Server 的方法

下载源文件 - 1.5 KB

引言

欢迎阅读我的 SQL 系列文章的第四部分。今天，我打算解决一个我经常在论坛中看到的问题，那就是如何将一个值列表传递给存储过程并使用它来选择或插入数据。我们将探讨三种实现方法并讨论每种方法的优缺点。

创建表

首先，我们需要一些示例数据。对于本次练习，我们假设有一个表存储姓名和生日，并以 ID 作为键。这是表的 SQL。

create table birthdays
(
  id int identity,
  firstName varchar(50),
  lastName varchar(50),
  birthday date
)

这是插入一些值的 SQL

insert into birthdays values ('Fred', 'Smith', '1969/02/17'), _
('Sarah', 'Smith', '1971/09/15'), ('Mary', 'Smith', _
'1996/07/20'),('Billy', 'Smith', '2000/07/01'), _
('Sarah', 'Jones', '1945/12/26')

如您所见，仅使用 SQL 就能用一行代码插入多个值。稍后将详细介绍。

现在，我们可能想要做的第一件事是查找具有相同姓氏或生日的人，例如

select * from birthdays where lastName = 'smith'

这显然很容易通过存储过程中的参数来实现。但是，如果我们有理由通过 ID 选择一组值，并且这些值的长度可以是任意的，那该怎么办？SQL 语句是：select * from birthdays where id in (1,3)。但是，如果您编写一个像这样的proc

create proc findById1 @ids varchar(20)
as
begin

	select * from birthdays where id in (@ids)
	
end

这会创建成功。现在执行以下操作

findById1 '1,3'

您的错误是

Msg 245, Level 16, State 1, Procedure findById1, Line 5
Conversion failed when converting the varchar value '1,3' to data type int.

SQL Server 无法为您拆分 CSV。它实际上正在这样做

Select * from birthdays where id = ‘1,3’

这当然不是我们想要的。

传入 CSL

最明显的做法是传入值并解析它。

CREATE proc findById2 (@list nvarchar(MAX))
AS
BEGIN

   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1

   	  select * from birthdays where id =  convert(int, substring(@list, @pos + 1, @valuelen))	
      SELECT @pos = @nextpos
   END
END

我们可以像这样运行它

findById2 '1,3'

这里有一些问题。最主要的问题是每行都会得到一个表。但代码本身确实很难看。让我们将所有内容都放到一个temp表中，看看它是什么样子

CREATE proc findById3 (@list nvarchar(MAX))
AS
BEGIN

declare @list nvarchar(MAX) = '1,3'

	create table #values
	(
	   id int,
	   birthday date,
	   firstname nvarchar(50),
	   lastname nvarchar(50)
	)

   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      insert into #values
   	  select id, birthday, firstname, _
      lastname from birthdays where id =  convert(int, substring(@list, @pos + 1, @valuelen))	
      SELECT @pos = @nextpos
   END
   
   select * from #values
   
END

您可以这样运行它

findById3 '1,3'

尝试更改列表中的值，看看会发生什么。所以，这种方法可行，但显然很难看。我们正在创建临时表，遍历string，做各种糟糕的事情。我不建议您这样做。我之所以将其包含在内，是为了向您展示不该如何做事情，以及向您展示一个您可能会在“野外”遇到的糟糕替代方案。在这种情况下，我的首选方法是使用 XML。

XML 处理

XML 在 SQL Server 2005 中成为一种有效的数据类型。在此之前，使用名为 OpenXML 的函数来解析 XML。如果您看到使用 OpenXML 并且您不在 SS2000 中的代码，我建议您进行转换。这是一篇关于使用 XML 不同方式的优缺点的好文章。简而言之，最好将值存储为属性，而不是存储为节点中的文本。所以，请随意阅读那篇文章，但是，我们主要使用带属性的 XML。使用 XML 的巨大优势当然是，如果您的调用来自过程代码（几乎肯定是），那么您很可能正在使用一种了解 XML 并提供各种工具来构建它的语言。即使是 .NET 中的 DataTable 也可以序列化为 XML，任何可序列化类也可以。所以，现在我们要创建一个接受此作为输入的存储过程

这是存储过程

create proc findByIdWithXml @xml xml
as
begin

;with fromXml as
(
SELECT Y.ID.value('(@val)[1]', 'int') as id
FROM @xml.nodes('/ids/id') as Y(ID)
)

select b.id, firstName, lastName, _
             birthday from birthdays b inner join fromXml x on x.id = b.id;

end

像这样调用它

findByIdWithXml '<ids><id val="1"><id val="3" /></id></ids>'

它返回

id	firstName	lastName	birthday
1	Fred	Smith	1969-02-17
3	Mary	Smith	1996-07-20

需要注意的一些事项：我们需要传入我们想要提取的对象的类型，作为一个string。如果我们在节点语句中选择/id，我们只会得到一个节点，即使它有许多子 id 节点，我们也只会选择一个。名称前的@表示它是一个属性。我们使用 [1] 是因为我读过这会使查询更快，尽管在实际应用中它是多余的（它意味着获取组中的第一个值）。这绝对比使用 CSL 整洁得多。但是，它也更强大。现在我们可以定义不止一种方法来获取值。让我们试试这个

create proc findWithXml @xml xml
as 
begin

;with fromXML as
(
SELECT 
Y.bday.value('(@id)[1]', 'int') as id,
Y.bday.value('(@fname)[1]', 'varchar(255)') as fname
FROM @xml.nodes('/birthdays/bday') as Y(bday)
)
select b.id, firstName, lastName, _
    birthday from birthdays b inner join fromXml x on x.id = b.id or x.fname = b.firstName;

end

现在我们允许两种不同类型的搜索，ID 和名字。显然，我们可以根据需要进行扩展。CTE 返回以下内容

id	fname
1	NULL
NULL	Sarah
4	NULL

但这很好，因为 SQL 中NULL不等于NULL。所以即使源表中有null值（我允许的），它也不会匹配它们。

findWithXml '<birthdays><bday id="1"><bday fname="Sarah"><bday id="4" /></birthdays>'

返回这个

id	firstName	lastName	birthday
1	Fred	Smith	1969-02-17
2	Sarah	Smith	1971-09-15
5	Sarah	Jones	1945-12-26
4	Billy	Smith	2000-07-01

请注意，我使用名称“Sarah”已确认这将返回多个匹配项。另请注意，我在我的存储过程中没有使用“ORDER BY”，因此它们没有按id顺序排列。当然，这很容易解决。

使用 XML 进行插入

当然，既然我们已经转向了强类型格式，我们也可以开始使用 XML 来执行插入操作（当然，我们总是可以执行删除操作，因为我们只删除一个值，就像我们select一样）。这个概念在所有情况下都是相同的，使用 CTE 创建内存中的表，然后将其与我们现有的表一起使用。

create proc insertWithXml @xml xml
as
begin

;with fromXML as
(
SELECT 
Y.bday.value('(@fname)[1]', 'varchar(255)') as fname,
Y.bday.value('(@lname)[1]', 'varchar(255)') as lname,
Y.bday.value('(@date)[1]', 'date') as bday
FROM @xml.nodes('/birthdays/bday') as Y(bday)
)

insert into birthdays (firstname, lastname, birthday)
select fname, lname, bday from fromXml;

end

注意列名不必相同，只需顺序相同即可。

insertWithXml '<birthdays><bday fname="Hannah" lname="Jones" _
date="1972/12/31"><bday fname="Calvin" lname="Kaputnik" date="1914/11/11" /></birthdays>'

现在选择所有并查看结果

select * from birthdays

已插入两个值。请注意，如果您有大量记录，这不如使用适当的批量插入过程高效，但如果您需要一次插入少量记录并希望调用一个存储过程一次而不是 X 次，则它非常有用。

表值参数

从 SS2008 开始，可以将表作为参数传递。虽然我们上面的代码相当紧凑，但将 XML 转换为表还有一个单独的步骤，尽管我从未遇到过问题，但我被告知有一些需要注意的怪癖。所以，这看起来更好，对吗？有一些陷阱需要注意。首先，存储过程中的 TVP 必须标记为只读（这意味着您也不能向其中插入或从中删除）。这似乎是合理的。其次，TVP 要求您首先定义它们。这意味着如果您的存储过程需要 TVP，您还需要在 SQL Server 实例上安装 TVP 才能使其工作，并且您最终可能会得到一个充满 TVP 的数据库，这在我看来比仅仅使用一种本机支持的类型更令人困惑。但我能看到的真正问题是，据我所知，Entity Framework 不支持 TVP。这是一个真正的打击，如果您可以创建一个类的列表并将其传递给存储过程，那将非常强大。就目前而言，这意味着即使您不使用 EF，使用 TVP 也会关闭您为针对您的数据库编写的任何其他项目使用 EF 的大门。我知道我经常不得不编写小型的一次性应用程序，而让 EF 免费创建我的数据层一直是一个非常有力的使用理由。考虑到这些注意事项，让我们继续。

CREATE TYPE BirthdayTableType AS TABLE 
( 
  id int,
  firstName varchar(50),
  lastName varchar(50),
  birthday date
);
GO

这将创建实际的 TVP 类型。如您所见，它是一个表声明，带有一些小的语法更改。事实上，我复制并粘贴了我们表的代码来创建它，只是删除了标识规范（事实证明这是有效的，尽管它显然在很大程度上是无用的）。这是存储过程

CREATE PROCEDURE dbo.findWithTVP
    @TVP BirthdayTableType READONLY
    AS 
begin
    
    select * from birthdays b
    inner join @TVP t on t.id = b.id or t.birthday = b.birthday _
                      or t.firstname = b.firstname or t.lastname  = b.lastname
end

这当然比 XML 解决方案更整洁，而且在实用性和可读性方面，它与逗号分隔列表解决方案完全不同。虽然我们显然通常不会在 SQL 中创建 TVP，但这一部分实际上更痛苦。

DECLARE @tvp AS BirthdayTableType;
INSERT INTO @tvp (id, firstname, lastname, birthday) values(1, null, null, null)
INSERT INTO @tvp (id, firstname, lastname, birthday) values(null, null, 'kaputnik', null)
INSERT INTO @tvp (id, firstname, lastname, birthday) values(null, 'sarah', null, null)
INSERT INTO @tvp (id, firstname, lastname, birthday) values(null, null, null, '1996-07-20')

exec findWithTVP @tvp

这将返回我们期望的记录。而且，同样，我们可以很容易地使用 TVP 编写插入代码，并且，我相信在普通的（非 EF） .NET 代码中，可以将数据表作为参数传递。因此，这会使具有传统数据层的代码变得整洁，其中一个存储过程的结果需要传递给另一个存储过程。

结论

因此，我们讨论了多种方法来将任意数量的参数传递给存储过程，以便我们可以对一组值而不是单个值进行操作。当涉及到选择一系列值时，这确实是唯一体面的可能性，尽管对于insert或delete而言，更多地是效率问题，我们可以进行一次 SQL 调用而不是多次。就我个人而言，我认为 XML 是一个通用标准，易于理解，易于使用，并且开箱即用。如果 TVP 有 EF 支持，我倾向于在大型项目中使用 EF，因为会有明显的优势。就目前而言，我认为它就像 SQL 中的许多东西一样——大多数事情都有不止一种方法，哪种方法最好，取决于您的情况。

历史

2014 年 1 月 4 日：初始版本