SQL中实现数组的功能

SQL Server并没有数组类型，ANSI SQL-92标准中并没有任何有关数组方面的定义。要实现其他高级语言中的数组功能，我们必须使用一些特殊的处理方法，其中包括特殊设计的字符参数、临时表、XML等。
方法1：使用特殊设计的字符参数来模拟数组。
我们可以用VARCHAR数据类型来模拟一个数组，数组中元素用逗号隔开，接着通过WHILE循环使用CHARINDEX以及SUBSTRING函数来提取其中的元素。
实例代码如下：
create proc 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
该示例代码可以求出一个整型数据数组的和，调用方式为：
exec sum_of_array @list='1,2,3,4,5'
结果为：16
方法2：利用WHERE ... IN ... 语句配合特殊设计的字符串来实现数组
create proc 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'''
方法3：使用临时表
调用存储过程之前构造一张临时表，将数组的内容逐行存放在表中，把表作为参数传递给存储过程。存储过程将表的内容再次读到一张临时表中，从而得到数组的内容。
create proc 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
方法4：还可以使用XML来实现
use pubs
go
create proc 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