在SQL SErver中实现数组功能

在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