最近一直在做dnn模块的开发,过程中碰到这么一个问题,需要同时插入n条数据,不想在程序里控制,但是sql sever又不支持数组参数.所以只能用变通的办法了.利用sql server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"。然后在存储过程中用substring配合charindex把分割开来.
详细的存储过程
create procedure dbo.productlistupdatespeciallist
@productid_array varchar(800),
@moduleid int
as
declare @pointerprev int
declare @pointercurr int
declare @tid int
set @pointerprev=1
set @pointercurr=1
begin transaction
set nocount on
delete from productlistspecial where [email protected]
set @pointercurr=charindex(',',@productid_array,@pointerprev+1)
set @tid=cast(substring(@productid_array,@pointerprev,@[email protected]) as int)
insert into productlistspecial (moduleid,productid) values(@moduleid,@tid)
set @pointerprev = @pointercurr
while (@pointerprev+1 < len(@productid_array))
begin
set @pointercurr=charindex(',',@productid_array,@pointerprev+1)
if(@pointercurr> 0)
begin
set @tid=cast(substring(@productid_array,@pointerprev+1,@[email protected]) as int)
insert into productlistspecial (moduleid,productid) values(@moduleid,@tid)
set @pointerprev = @pointercurr
end
else
break
end
set @tid=cast(substring(@productid_array,@pointerprev+1,len(@productid_array)[email protected]) as int)
insert into productlistspecial (moduleid,productid) values(@moduleid,@tid)
set nocount off
if error=0
begin
commit transaction
end
else
begin
rollback transaction
end
go
网友bizlogic对此的改进方法:
应该用sql2000 openxml更简单，效率更高，代码更可读：
create procedure [dbo].[productlistupdatespeciallist]
(
@productid_array nvarchar(2000),
@moduleid int
)
as
delete from productlistspecial where [email protected]
-- 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 新闻热点
疑难解答
