SQL 的存储过程或着自定义函数,有时候,我们希望类似传递一个数组的对象给这个存储过程或着自定义函数.
但是 SQL 并不支持数组的输入参数。
这时候,我们一般是传递一个合并后的字符串给存储过程,然后在存储过程中解析这个字符串。
这个技术,很多地方都有收集,今天在 Arrays and Lists in SQL Server http://www.sommarskog.se/arrays-in-sql.html 看到一个经典的总结。这个地方涉及到的一些总结函数,我就不再提到了。非常感谢 Erland Sommarskog, SQL Server MVP.
给了我们这个丰富的,SQL 中如何处理数组的总结。
下面把一段字符串解析成Guid数组的SQL自定义函数,上述文章中并没有提供,我是改写自其中的 iter_charlist_to_table 自定义函数来实现的。
郭红俊提供的SQL自定义函数调用范例如下:
select * from iter_uniqueidentifierlist_to_table(
'
0E099B0F-AFE3-4FCA-B634-014DF91AA79B,
7EF06D1D-081D-4957-97A1-015CAA4B00E7,
B18BD78B-77F5-4AA9-A3B8-016776176924,
BB1E54BE-BB91-4FE4-8267-0234BD798A1D,
B74FC01A-F33E-4CF3-8B79-02391D5E89BD,
94DE017F-0C7E-4750-94CD-0266C38C4D71,
CF94841D-87D3-4792-A886-0269745796A6'
,',')
这个自定义函数的实现如下:
--
-- http://www.sommarskog.se/arrays-in-sql.html
--
-- 把一个字符串组成的 uniqueidentifier 列表转换成一个 table
CREATE FUNCTION iter_uniqueidentifierlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
guidID uniqueidentifier NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000),
@id uniqueidentifier
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = left(ltrim(rtrim(@tmpstr)), @pos - 1)
SET @tmpval = replace(@tmpval,char(9),'')
SET @tmpval = replace(@tmpval,char(10),'')
SET @tmpval = replace(@tmpval,char(13),'')
-- print( @tmpval)
set @id = convert(uniqueidentifier,@tmpval);
INSERT @tbl (guidID) VALUES(@id)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
-- print @leftover
SET @leftover = ltrim(rtrim(@leftover))
SET @leftover = replace(@leftover, char(9),'')
SET @leftover = replace(@leftover, char(10),'')
SET @leftover = replace(@leftover, char(13),'')
set @id = convert(uniqueidentifier,@leftover);
INSERT @tbl (guidID) VALUES(@id)
END