如何给存储过程，传一个数组参数？

接到这个需求，本以为简单。谁知道SQL不支持数组。于是想用','分割传进去，哪知道SQL居然没有split()函数，还得用substring & charindex，坑爹啊。

方法一 分割

例:通过SQL Server存储过程传送数组参数删除多条记录

eg. ID 值为'1,2,3' 以下存储过程就是删除表中id号为1,2,3的记录：

CREATE PROCEDURE DeleteNews 
    @ID nvarchar(500) 
as 
    DECLARE @PointerPrev int 
    DECLARE @PointerCurr int 
    DECLARE @TId int 
    Set @PointerPrev=1 
     
    while (@PointerPrev < LEN(@ID)) 
    Begin 
        Set @PointerCurr=CharIndex(',',@ID,@PointerPrev) 
        if(@PointerCurr>0) 
        Begin 
            set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int) 
            Delete from News where ID=@TID 
            SET @PointerPrev = @PointerCurr+1 
        End 
        else 
            Break 
    End 
    --删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除 
     set @TId=cast(SUBSTRING(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int) 
     Delete from News where ID=@TID 
GO

 

这个方法麻烦不？于是又有另外一种方法——临时表

 

方法二 Table对象

 

传3个参数，都是数组形式还有时间类型用存储过程更新

 

@Oid = 1,2,3,4

 

@Did = 111,222,333,444

 

@DateArr = '2007-1-1,2007-1-2,2007-1-3,2007-1-4'

 

 

 

CREATE proc Test999

 

@Oid nvarchar(1000)    --ID1

 

,@Did nvarchar(1000)   --ID2

 

,@DateArr nvarchar(1000) --日期

 

AS

 

DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000)

 

set @id1s=@Oid       

 

set @id2s=@Did       

 

set @dates = @DateArr

 

-- 调用函数实现处理

 

SELECT @id1s=@id1s, @id2s=@id2s,@dates = @dates

 

 

 

UPDATE A SET terminate_time = B.dt

 

FROM [Table] A,(

 

SELECT

 

    id1 = CONVERT(int, Desk_id.value),

 

    id2 = CONVERT(int, room_id.value),

 

    dt = CONVERT(datetime, terminate_time.value)

 

FROM dbo.f_splitstr(@id1s) Desk_id, dbo.f_splitstr(@id2s) room_id, dbo.f_splitstr(@dates) terminate_time

 

WHERE Desk_id.id = room_id.id

 

    AND Desk_id.id = terminate_time.id

 

) B

 

WHERE A.Desk_id = B.ID1 AND A.room_id = B.ID2

 

GO这个还用到一个函数f_splitstr

 

CREATE FUNCTION dbo.f_splitstr(

 

    @str varchar(8000)

 

)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))

 

AS

 

BEGIN

 

    DECLARE @pos int

 

    SET @pos = CHARINDEX(',', @str)

 

    WHILE @pos > 0

 

    BEGIN

 

        INSERT @r(value) VALUES(LEFT(@str, @pos - 1))

 

        SELECT

 

            @str = STUFF(@str, 1, @pos, ''),

 

            @pos = CHARINDEX(',', @str)

 

    END

 

    IF @str > ''

 

        INSERT @r(value) VALUES(@str)

 

    RETURN

 

END

这个方法更加可怕~~~辗转百度，找到了一个还不错的方法，用OPENXML，这个SQL2000就支持了。

 

方法三 xml

 

 

 

应该用SQL2000 OpenXML更简单，效率更高，代码更可读：

 

CREATE Procedure [dbo].[ProductListUpdateSpecialList] 
( 
@ProductId_Array NVARCHAR(2000), 
@ModuleId INT 
)

 

AS

 

delete from ProductListSpecial where ModuleId=@ModuleId

 

-- If empty, return 
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0) 
RETURN

 

DECLARE @idoc int

 

EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array

 

Insert into ProductListSpecial (ModuleId,ProductId) 
Select 
@ModuleId,C.[ProductId] 
FROM 
OPENXML(@idoc, '/Products/Product', 3) 
with (ProductId int ) as C 
where 
C.[ProductId] is not null

 

EXEC sp_xml_removedocument @idoc

哇，看起来还是很复杂的说。有木有更好的办法呢？

既然是OPENXML，为啥不用XML呢？于是查到，SQL2005以上都支持XML。Good，找到一片天了。

利用SQL2005的XML/XQuery功能，可以很方便的解决传数组参数的问题。

 

 

declare @xml xml
set @xml = '<?xml version="1.0"?>
<ArrayOfInt>
<int>1</int>
<int>2</int>
<int>3</int>
</ArrayOfInt>'

 

select N.value( '(text())[1]','int' ) RoomId from @xml.nodes('/ArrayOfInt/int') V(N)

结果就是

 

注：上面的数据类型为XML

这样就可以给存储过程传一个集合了，一般是数组，比如主键的集合。然后可用通过主键集合来查询记录。

客户端可用使用序列化，把list转化成xml。不过在序列化过程中，遇到了一些小麻烦。

1. .net默认是utf-16，SQL只认识utf-8

2. 出现很讨厌的xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

可以用我这个类

    public static class SerializeHelper
    {
        private static readonly XmlSerializerNamespaces Namespaces = new XmlSerializerNamespaces();

        static SerializeHelper()
        {
            //去掉 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            Namespaces.Add(string.Empty, string.Empty);
        }

        public static string SerializeXml<T>(T obj)
        {
            XmlSerializer serializer = new XmlSerializer(typeof(T));
            using (MemoryStream stream = new MemoryStream())
            {
                serializer.Serialize(stream, obj, Namespaces);
                return Encoding.UTF8.GetString(stream.ToArray());
            }
        }

        public static T DeserializeXml<T>(string obj)
        {
            XmlSerializer serializer = new XmlSerializer(typeof(T));
            using (StringReader reader = new StringReader(obj))
            {
                return (T)serializer.Deserialize(reader);
            }
        }
    }

SQL与XML，XQuery结合起来，功能会很强大的。

从这个事件也看出，我应该多关注SQL各个版本之间新增功能，比如SQL2008新增功能。否则遇到问题，找不到比较好的解决方案。

OK，问题以完美的方式解决了，用最妙的方法解决问题，好开森哦~~~O(∩_∩)O！