在SQLSErver中实现数组功能

在SQLSErver中实现数组功能
在SQL SErver中实现数组功能
本⽂摘⾃肖桂东编著的《SQL Server 疑难解析》
问题描述：
我需要向⼀个存储过程传递数组类型的参数，可是Transact-SQL⾥⾯没有数组类型，请问应该怎样实现数组的功能？
问题分析：
SQL Server并没有数组类型，ANSI SQL-92标准中并没有任何有关数组⽅⾯的定义。

要实现其他⾼级语⾔中的数组的功能，我们必须使⽤⼀些特殊的处理⽅法，其中包括特殊设计的字符参数、临时表、XML等。

问题解答：
第⼀种⽅法是使⽤特殊设计的字符参数来模拟数组。

我们可以⽤ varchar 数据类型来模拟⼀个数组，数组中的元素⽤逗号分隔开，接着通过WHILE循环使⽤CHARINDEX以及SUBSTRING函数来提取其中的元素。

⽰例代码如下：
CREATE PROCEDURE sum_of_array @list varchar(1000)
AS
DECLARE @ix int, @pos int, @str varchar(1000), @sum int
SET @pos = 1
SET @ix = 1
SET @sum = 0
WHILE @ix > 0
BEGIN
SET @ix = charindex(',', @list, @pos)
IF @ix > 0
SET @str = substring(@list, @pos, @ix - @pos)
ELSE
SET @str = substring(@list, @pos, len(@list))
SET @str = ltrim(rtrim(@str))
SET @sum = @sum + cast(@str AS int)
SET @pos = @ix + 1
END
SELECT @sum
GO
该⽰例代码可以求出⼀个整型数据数组的和,调⽤⽅式为：
EXEC sum_of_array @list = '1,2,3,4,5'
结果为：16。

第⼆种⽅法是利⽤ WHERE…IN…语句配合特殊设计的字符串来实现数组。

⽰例代码如下：
CREATE PROCEDURE query_sysobjects @array nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @nsql nvarchar(4000)
SET @nsql = '
SELECT *
FROM sysobjects
WHERE name IN (' + @array + ')'
EXEC sp_executesql @nsql
END
GO
该⽰例代码可以查询 sysobjects 表中指定对象的信息，对象由调⽤参数指定。

例如我们要查询sysobjects表中sysusers、sysindexes以及syscolumns三个对象的信息，则调⽤⽅式为：
EXEC query_sysobjects
@array = '''sysusers'',''sysindexes'',''syscolumns'''
第三种⽅法是使⽤临时表。

调⽤存储过程之前构造⼀张临时表，将数组的内容逐⾏存放在表中，把表作为参数传递给存储过程。

存储过程将表的内容再次读到⼀张临时表中，从⽽得到数组的内容。

⽰例代码为：
CREATE PROCEDURE mytest @MyParmTempTable varchar(30)
AS
BEGIN
CREATE TABLE #MyInternalList(list_item varchar(2) NOT NULL)
SET NOCOUNT ON
INSERT #MyInternalList EXEC ('select * from ' + @MyParmTempTable)
SELECT *
FROM sysobjects
WHERE type IN
(
SELECT list_item
FROM #MyInternalList
)
END
GO
该⽰例代码同样查询 sysobjects 表，返回所有类型为 S、U 和 P 的对象的信息。

调⽤之前要先构建⼀个表（也可以为临时表）：CREATE TABLE #MyList(list_item varchar(2) NOT NULL)
INSERT #MyList VALUES ('S')
INSERT #MyList VALUES ('U')
INSERT #MyList VALUES ('P')
GO
调⽤的时候只要向存储过程传递这个临时表即可：
EXEC mytest #MyList
GO
最后，我们还可以使⽤ XML 来实现。

⽰例代码如下：
USE pubs
GO
CREATE PROCEDURE usp_Array
@Array TEXT
AS
DECLARE @XMLDoc INT
EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @Array
SELECT *
FROM OPENXML (@XMLDoc , '/ROOT/Array', 1 )
WITH (Id INT, Value varchar(15)) AS TempArray
INNER JOIN authors ON authors.au_id = TempArray.Value
EXEC sp_xml_removedocument @XMLDoc
GO
调⽤的代码为：
DECLARE @doc varchar(500)
SET @doc ='<ROOT>
<Array Id="1" Value="172-32-1176"></Array>
<Array Id="2" Value="213-46-8915"></Array>
<Array Id="2" Value="238-95-7766"></Array>
</ROOT>'
EXEC usp_Array @doc
CHARINDEX：返回字符串中指定表达式的起始位置。

语法：CHARINDEX ( expression1 , expression2 [ , start_location ] )
参数介绍如下。

expression1：⼀个表达式，其中包含要寻找的字符的次序。

expression1 是⼀个短字符数据类型分类的表达式。

expression2：⼀个表达式，通常是⼀个⽤于搜索指定序列的列。

expression2 属于字符串数据类型分类。

start_location：在expression2 中搜索 expression1 时的起始字符位置。

如果没有给定 start_location，⽽是⼀个负数或零，则将从expression2 的起始位置开始搜索。

返回类型：
int。