給SQL Server傳送數組參數的變通辦法

2024-08-31 00:47:47

字體：大中小

來源：轉載

供稿：網友

最近一直在做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